Introduction

The scripting of database objects is required if you want to be able to generate the various database objects in your application programmatically. It is possible to query the system tables for fetching the script of the database objects or the use of system stored procedures is also possible. SQL-DMO has been available in the past for this need, but it is not so efficient in terms of memory, resources and network usage. One of the new feature of .NET Framework 2.0, SMO is a managed and enhanced version of SQL-DMO, which can be used for all the tasks performed by SQL-DMO, plus more new features. This article will cover the various way to get your scripts ready for transfer or recreate some database objects.

Various methods

For using system tables, you can refer to my other article Querying System Tables.

We will use a couple system stored procedures sp_help and sp_helptext.

EXEC sp_help ‘objectName’

This will return the Name, Owner, Type and Create Date Time of the objectName specified. Also the list of parameters will be shown for that objectName.

EXEC sp_helptext ‘objectName’

This will return the full text (script) of the objectName you have specified. One of the limitations with sp_helptext is that it does not script the table details i.e., the CREATE TABLE statement will not be generated. sp_help will work with tables and will provide details specified above and additionally the column details and also the reference keys, identities, etc.

The next method is to use SQL-DMO through which it is possible to script the database objects like tables, stored procedures, etc. It has a class library that allows it to perform various tasks like getting list of servers, various database objects and script them. Being a COM object, SQL-DMO has disadvantages associated with it like more memory consumption and resource usage. Also the installation related problems are very tricky.

Another method is to use SMO (SQL Server Management Objects). It is a .NET assembly and not a COM object, included in .NET Framework 2.0. Thus, managed code. It is possible to generate scripts for your database objects including CREATE TABLE scripts with more flexibility in providing various options for scripting each object. This text will go through the technique of getting the scripts for database objects using SMO (eg. stored procedure).

All the classes are available under the Microsoft.SqlServer namespace. Create a new .NET project and add a reference to the following three files located at installation folder of SQL Server("Microsoft SQL Server\90\SDK\Assemblies").

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.SmoEnum.dll

//DECLARE SERVER OBJECT AND SPECIFY THE SERVERNAME
Server theServer = new Server(“ServerName”);
//DECLARE DATABASE OBJECT AND SPECIFY THE DATABASE TO CONNECT
Database myDB = theServer.Databases["Test_db"];
//STRING COLLECTION OBJECT TO STORE THE SCRIPT
System.Collections.Specialized.StringCollection strColl = new System.Collections.Specialized.StringCollection();
//DECLARE STORED PROCEDURE OBJECT ON THE DATABASE
StoredProcedure sourceSp = myDB.StoredProcedures["sp_testScript"];
                
//DECLARE ARRAY OF SMO OBJECT
//CAN CONTAIN VARIOUS DATABASE OBJECTS
SqlSmoObject[] smoObj = new SqlSmoObject[1];
smoObj[0] = sourceSp;
//DECLARE SCRIPTOR OBJECT FOR THE SERVER
Scripter scrip = new Scripter(theServer);
//SCRIPT() FUNCTION RETURNS THE SCRIPT FOR THE SMO OBJECTs
strColl = srcip.Script(smoObj);

Similarly, it is also possible to script the database object directly without using the SCRIPTOR class.

StoredProcedure sourceSp = myDB.StoredProcedures["sp_testScript"];
strColl = sourceSp.Script();

Apart from scripting your database objects, it is also possible to specify whether to script FOREIGN KEYS, INDEXs, IDENTITY columns and also IF NOT EXISTS clause. To specify this options use,

scrip.Options.IncludeIfNotExists = true;
scrip.Options.NoIdentities = true;

Here scrip is the object of SCRIPTOR class.

The same above steps can be used for user defined function, views, tables and so on.

Table myTable = myDB.Tables[“TableName”];
UserDefinedFunction myFunction = myDB.UserDefinedFunction[“FunctionName”];

Conclusion

Using various classes provided by SMO, it is possible to transfer, backup and recovery of the database objects in a simple and efficient manner.