By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

provide enough flexibility to generate them the way you would like. For that reason, database developers and administrators have been coding custom script generation utilities and implementing automated scripting capabilities.

For example, many companies have automated the process of scripting database objects such as stored procedures and views into text files, and then checking them into a source control system like Microsoft Visual SourceSafe. Starting with SQL Server 2005, however, SQL Server Management Objects (SMO) provide the necessary API for generating database object scripts, so let’s take a look at the basics of SMO scripting and some tips to help you get started.

The fundamentals of SMO scripting are very simple. Once you are connected to a server, select the object you want to use and invoke the Script() method. Any database object that can be created with a SQL script will have the Script() method implemented in SMO. This method also accepts an optional parameter called ScriptingOptions, which is a collection of possible scripting options with mainly true/false values that define what scripts the output should include. Learning what they are and how to use them is the most important part of programming SMO scripting.

The following code shows the scripting options included in the above sample application:

The first six options accept true or false boolean values, while the TargetServerVersion property determines what version of SQL Server should be targeted. The property type is the SqlServerVersion object and it supports three versions – 2000, 2005 and 2008.

In your code you can script individual objects or script all objects of a certain type. The following code shows how you can loop through all stored procedures in the database and script them into individual files:

Similarly, you can loop through other object types such as Views or UserDefinedFunctions. Note, however, that the code skips system objects since it’s highly unlikely that you are interested in scripting a system of stored procedures. In fact, you will get an error if you try to script system objects since the code is not scriptable.

The code calls my generic function -- ScriptObject() -- and the first parameter is an SMO interface called IScriptable. This interface allows you to cast any scriptable object into IScriptable, allowing you to invoke the Script() method on IScriptable rather than on the object type. This way I can have one generic function to handle multiple object types. For example, I can then script a view with the same function – I just have to cast the view into IScriptable the same way I did with stored procedures in the above code:

The code does some special handling for a couple of scripting options. For instance, if you set the options to generate a DROP statement, it will not generate the CREATE statement. Therefore, my code calls the script method twice – once to generate the DROP statement and once to generate the CREATE statement. After DROP is generated, I can set the ScriptDrops option to false and allow CREATE to be generated.

The IncludeIfNotExists option is even trickier. If it is true together with ScriptDrops it will generate a conditional DROP looking something like this:

The code is generated in this format because CREATE has to be the first command in a batch for many objects. Therefore, you cannot run IF EXISTS in front of it. Since I don’t really like this format -- and I know many other DBAs feel the same -- I set this option to false after the DROP statement has been scripted. As a result, I end up dropping the object if it exists and then creating it, as opposed to creating it only if it doesn’t exist.

Also, you may have noticed that I preserved two settings in the code (ScriptDrops and IncludeIfNotExists) and at the end of the function I restore the original values. The reason for this is because the function gets called repeatedly for different objects and since we need to modify the settings inside the function for generating the CREATE script, we also need to set them back so the next object gets scripted the same way.

Here is an example of a script generated by my application after specifying to generate a conditional DROP, descriptive headers, database context and extended properties:

Figure 2: Conditional DROP script (click to enlarge)

So those are the basics of SMO scripting, but as always there is no substitution for playing with the code, setting different scripting options and examining it. Doing so should help you generate SQL code exactly how you like it.

ABOUT THE AUTHORRoman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy