This month, I will provide you another very helpful tool if you are working with Microsoft SQL Server databases. This tool generates the scripts to create database objects (stored procedures, tables, views, user-defined functions, and user-defined types).

This is useful in at least 2 scenarios. The first scenario where it is very useful is to be able to track the changes to the databases objects over the time, it would be possible to revert your database to a previous state. The second scenario that you will probably use the most is to find where a particular object or field is used. Because the scripts are all persisted into a table of a distinct database, it is very easy to query that table for that specific object or field name.

So what it is?

This month project was created using Visual Studio 2008 SP1 referencing Microsoft SQL Server 2008 components. It would be easy for you to use this code in VS2005 as no new .Net Framework 3.5 features are used. Also, if you have Microsoft SQL Server 2005 (instead of 2008), you could change the references and it would work exactly the same.

Lucky are C# developers. The downloadable solution also contains a C# version of this project.

I run this application from the Windows scheduler on a daily basis around 2 AM. I run it once for each database I have. It starts by loading the latest scripts for a particular database, then create the current script and detect if that script has changed or not (and only save the new version if it has changed).

I created it has Windows application (even if I launch it from the Windows scheduler) because it is a lot easier to debug. It has a very simple interface (a Button and a ListBox for debugging).

Starting the tool

You may have already guessed that because the application is normally launched using the Windows scheduler and that it can be used against many different databases, you will need to pass arguments to this application.

I won’t explain this topic here because I have published a complete article on that subject titled “Passing arguments to an application start up” last November.

This application takes many arguments as you can see in the figure 1.

Figure 1: The arguments

Here is the list of the various arguments you can pass to this application:

The arguments are not case-sensitive and you can provide them in any order you want.

For those who are looking the code, you will find the parsing of the command line arguments in the GetCommandLineArgs method.

After the arguments have been parsed, the form will be displayed only if there is anything to do. The only thing that the form does is to launch the real code which is in the cDBScripts class and display statuses in its ListBox.

So how do we script objects?

This is the interesting part of this article. The cDBScripts class contains only 2 public methods. The first method is named Connect and, as its name implies, it connects to the SQL Server. It also creates an instance of a Database object from which we will read objects. And finally it checks if the database that will contains the scripts exist (and creates the database and the table if it doesn’t).

Instead of using plain-old ADO.Net objects, I use Microsoft SQL Server objects because those provide us all the required method to generate scripts very easily. As you can see in figure 2, there are 4 components to add to your project’s references.

Figure 2: References to the required SQL Server components

The other public method is named DoTasks. This is the method that will create the scripts for the tasks required by the command line. Each kind of object (SPROC, TABLE, UDF, UDT, VIEW) branches to a different private method because each one is a bit different from the other while being very similar.

The pattern is always the same. We first need to loop through all the objects of a particular type. For example, this is the code you need to loop through all the stored procedures of the current database:

Private Sub ProcessStoredProcs()
    RaiseEvent ProcessingObject("Stored Procedures", "", "")
    For Each objX As StoredProcedure In Me.Database.StoredProcedures
        If Not objX.IsSystemObject Then
            CheckScript(objX.Name, ScriptStoredProc(objX))
        End If
    Next
End Sub

The ScriptStoredProc method has the following code:

Public Function ScriptStoredProc(ByVal pStoredProc As StoredProcedure) As String
    Dim opt As New ScriptingOptions
    opt.DriAll = True
    opt.Default = True
    opt.Indexes = True
    opt.ClusteredIndexes = True
    opt.NonClusteredIndexes = True
    opt.ExtendedProperties = True
    opt.Triggers = True

    Dim col As Specialized.StringCollection = pStoredProc.Script(opt)
    Dim en As Specialized.StringEnumerator = col.GetEnumerator
    Dim objSB As New System.Text.StringBuilder(5000)

    While en.MoveNext
        objSB.AppendLine(en.Current)
        objSB.AppendLine(Environment.NewLine)
    End While

    Return objSB.ToString
End Function

The CheckScript method (download the code to see it) compares the script just generated with all the current scripts to determine its state (new, modified, or unchanged). If the state is new or modified, a SQL query is created to add this script to the database. In order to save roundtrips to the server, this query is concatenated to all the others and will be sent in batch at the end.

After all the tasks have created their scripts, the DetectDeletedObjects is called to create DELETE queries for objects that no longer exist in the database.

Finally, the UpdateDatabase is called to run the big string containing all the modifications. This is where your table in your script database will get updated.

A note on the listener

If you have opened the code, you probably found many Trace.WriteLine statements. Because this application runs through the Windows scheduler, we need some help when we find that the application is not running as supposed and/or to keep the trace of the execution. Once again, I won’t explain how tracing and listeners are working because I have already did that in November 2003 in an article titled “Code diagnostic (an article on tracing and debugging)”.

Deploying your application

If you need to deploy this application on a computer that does not have SQL Server 2008 already installed (it may have a previous version like SQL 2005), you will need to install some requirements. You will find 2 installation packages that are installing these requirements from Microsoft Downloads.

• Microsoft SQL Server System CLR Types
• Microsoft SQL Server 2008 Management Objects

Install these 2 packages and your application should work like a charm!

Querying the scripts database

Now that you have the script for your entire database object in a table of your database, it is very easy to query that table to satisfy all your ad hoc queries. Here are 3 typical queries for you to extract some data of that table.

This first query shows how to list all the modifications on a particular object (in this case the Applications table) of a specific database (in this case the Security_PRD database):

SELECT * 
FROM TB_sysDBscripts
WHERE scriptDBName = 'Security_PRD'
AND scriptName = 'Applications'
ORDER BY scriptDateTime DESC 

SELECT * 
FROM TB_sysDBscripts AS S1
WHERE S1.scriptDBName = 'Security_PRD'
AND S1.scriptID = (
	SELECT MAX(ScriptID) 
	FROM TB_sysDBscripts 
	WHERE scriptDBName = S1.scriptDBName
	AND scriptName = S1.scriptName)
ORDER BY S1.scriptName

SELECT * 
FROM TB_sysDBscripts AS S1
WHERE S1.scriptDBName = 'Security_PRD'
AND S1.scriptText LIKE '%IDUser%'
AND S1.scriptID = (
	SELECT MAX(ScriptID) 
	FROM TB_sysDBscripts 
	WHERE scriptDBName = S1.scriptDBName
	AND scriptName = S1.scriptName)
ORDER BY S1.scriptName

Conclusion

This is a very useful tool; at least it is for me.

Using the Managements Objects component provided with SQL Server itself, scripting an object is really easy.

And now that you have all your scripts into a table, using T-SQL to query gives you enough flexibility to query this valuable source of information.