Generate Scripts for database objects with SMO for SQL Server
Written By: Arshad Ali -- 9/9/2009
From: http://www.mssqltips.com/tip.asp?tip=1833
Problem
In my last article, Getting started with SQL Server Management Objects (SMO) , I discussed what SMO is, how you can start working with SMO, how to connect to SQL server, how to enumerate through the different SQL objects, create a database, create a table etc, all programmatically.
In this tip I would like to take you on an SMO ride to generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically.
Solution
As I discussed in my last tip, SQL Server objects are represented as object hierarchies inside SMO, for example a Server object is a collection of Database objects. A Database object is a collection of a Table (though there are couple of other collection inside the Database object as well such as Stored Procedure, Views, User-defined Functions etc). A Table is a collection of a Column and so on.
Every object in this hierarchy has a method called a script , which returns a string collection of scripts. Apart from that, SMO provides a utility class, Scripter , which generates the script in a more efficient way. For example, the Scripter class can discover the relationships between objects and can provide scripts for dependencies as well and it can respond to Progress and Error events.
Usage Scenario
As I said, SQL Server Management Studio (SSMS) provides a wizard type interface to script out all or selected objects, but there might be some scenarios, some of them are discussed below, where you would consider the use of SMO instead of SSMS.
- You want to create a tool which will automatically set up (or sync) an environment for dev or test which resembles the production environment.
- You have a data warehouse database, in this database data is not that important (or even the data size is so big, taking a backup of the entire database would not be feasible) as data can again be pulled from the source systems but you want to make sure the schema objects are scripted and backed up automatically so that you can re-create the database in case of a disaster.
- Programmatically you want to control the backup and restore process of database administration (I will cover this in more detail in another tip "Backup and Restore Programmatically with SMO").
- Programmatically you want to transfer a database schema and data to another instance of SQL Server. (I will cover this in more detail in another tip "Transferring schema objects and data programmatically with SMO").
Example
Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. For more details on what these assemblies are and how to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO) .
C# Code Block 1 - Here I am using the Scripter utility class to generate the script for two selected databases. Two Database objects are created first; one of them refers to the AdventureWorks database and another one refers to AdventureWorksDW. The script method of the scripter object is called which takes database object URN (Unique Resource Name) as input and returns a string collection of scripts. URN is a new concept in SMO (this was not available in SQL-DMO) which provides similar notation like XPath to denote object hierarchy.
C# Code Block 1 - Generating Database Script |
Scripter scripter = new Scripter (myServer); |
C# Code Block 2 - In this code block I am generating CREATE TABLE scripts for all the tables in the AdventureWorks database. As said before, a database is a collection of tables, so I am enumerating through the table collection of the database to generate a script for each table. Along with that I am also using the ScriptOptions class to specify the different scripting options, for example in this code I am scripting IF NOT EXISTS and DROP TABLE scripts as well.
C# Code Block 2 - Generating scripts for table collection |
Scripter scripter = new Scripter (myServer); Database myAdventureWorks = myServer.Databases["AdventureWorks" ];/* With ScriptingOptions you can specify different scripting * options, for example to include IF NOT EXISTS, DROP * statements, output location etc*/ scriptOptions = new ScriptingOptions (); scriptOptions.ScriptDrops = true ; scriptOptions.IncludeIfNotExists = true ; foreach (Table myTable in myAdventureWorks.Tables) { /* Generating IF EXISTS and DROP command for tables */ StringCollection tableScripts = myTable.Script(scriptOptions); foreach (string script in tableScripts) Console .WriteLine(script); /* Generating CREATE TABLE command */ tableScripts = myTable.Script(); foreach (string script in tableScripts) Console .WriteLine(script); } |
C# Code Block 3 - This code block further extends the use of the Scripter and ScriptOptions classes to generate a script for a table (HumanResources.EmployeeAddress) along with all the other objects on which this table depends on. The ScriptOptions class also provides several properties for DRI (Declarative Referential Integrity) objects. You can either select individual DRI objects or all to script out along with the main object script.
C# Code Block 3 |
Scripter scripter = new Scripter (myServer); |
C# Code Block 4 - This code block provides CREATE TABLE scripts for all the AdventureWorks' tables along with all the indexes of each table. Here I am using IndexCollection class to enumerate through index collection of the table to generate CREATE INDEX scripts for all the indexes on the given table.
C# Code Block 4 |
Scripter scripter = new Scripter (myServer); |
C# Code Block 5 - In my last tip on SMO, I showed you how you can use SMO to create a database and a table on the server programmatically. In this code block, I am using the same code, but this time before creating the objects on the server I am generating database and table scripts. This means it is not required to create objects on the server in order to generate scripts, if you have objects in memory you can generate scripts for these objects as well even without creating on the server.
C# Code Block 5 |
/* Create database called, "MyNewDatabase" */ |
The complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.
Note
- If you have an application written in SQL-DMO and want to upgrade it to SMO, that is not possible, you will need to rewrite your applications using SMO classes.
- SMO assemblies are installed automatically when you install Client Tools.
- Location of assemblies in SQL Server 2005 is C:/Program Files/Microsoft SQL Server/90/SDK/Assemblies folder.
- Location of assemblies in SQL Server 2008 is C:/Program Files/Microsoft SQL Server/100/SDK/Assemblies folder.
- SMO provides support for SQL Server 2000 (if you are using SQL Server 2005 SMO it supports SQL Server 7.0 as well) but a few namespaces and classes will not be supported in prior versions.
- Scripter and ScriptingOptions classes are available in Microsoft.SqlServer.Smo assembly (in microsoft.sqlserver.smo.dll) and Microsoft.SqlServer.Management.Smo namespace.
- User needs to have permissions on the objects in order to generate scripts for it.
Next Steps
- Review Getting started with SQL Server Management Objects (SMO) tip.
- Review Overview (SMO) on MSDN.
- Review Scripter Class on MSDN
- Review ScriptingOptions Class on MSDN.
- Review Generate scripts for SQL Server 2008 object migration tip.
Readers Who Read This Tip Also Read