Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Script Database Objects with SMO

By Vasant Raj,

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.
Total article views: 14888 | Views in the last 30 days: 30
 
Related Articles
FORUM

Script to check for last updates in database objects

Script to check for last updates in database objects

FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

ARTICLE

How to Get the Scripts for SQL Server Objects

Using VB Script allows one to script out all the Sql Server objects in independent file.

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

BLOG

Backup Database Object

I saw this question in one of forums on backing up i.e. scripting out a database object. The problem...

Tags
smo    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones