SMO Scripting Capabilities - Generate Table Create Script Without Physically Creating SQL Server Table
by David Hayden ( .NET Developer )
One of the really interesting aspects of SQL Server Management Objects is that you can work with SMO Objects ( databases, tables, stored procedures, etc. ) in memory without physically creating the objects in SQL Server. This means you can generate scripts for the creation of a table, for example, that only exists in memory.
I have talked about creating tables and stored procedures using SMO and C# before:
but those were physically created on the server. Here I will create another table using SMO and C#, but only do it in memory. I will then call the Script method on the table to retrieve the proper DDL for the creation of the table. Note that nowhere in the application do I call Create, which would actually create the table in SQL Server.
Create Table Using SMO and C#
The simple SMO Application to create a table is as follows. One of the things I added in this example is to show how to create a default constraint. In this example, the ID, which is a UniqueIdentifier, calls newid() by default to get its value if one is not provided.
// Get Instance of Local SQL Server
Server server = new Server();
// Create In-Memory Database
Database db = new Database(server,"Test");
// Create In-Memory Table
Table contacts = new Table(db, "Contacts");
// Create ID Column and Constraint
Column id = new Column(contacts,
"ID", DataType.UniqueIdentifier);
id.AddDefaultConstraint("DF_Contacts_ID")
.Text = "newid()";
// Create FirstName and LastName Columns
Column firstName = new Column(contacts,
"FirstName", DataType.NVarChar(50));
Column lastName = new Column(contacts,
"LastName", DataType.NVarChar(50));
// Create a PK Index on Table
Index index = new Index(contacts, "PK_Contacts");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
// PK Index is 1 column - "ID"
index.IndexedColumns.Add(new
IndexedColumn(index, "ID"));
// Add the new index to the table.
contacts.Indexes.Add(index);
// Add Columns to Table
contacts.Columns.Add(id);
contacts.Columns.Add(firstName);
contacts.Columns.Add(lastName);
// Make Sure Constraint is Scripted
ScriptingOptions options = new ScriptingOptions();
options.Add(ScriptOption.DriAllConstraints);
// Get Script for Table
// It Only Exists in Memory
StringCollection script = contacts.Script(options);
The script generated looks like the following:
CREATE TABLE [dbo].[Contacts]
(
[ID] [uniqueidentifier] CONSTRAINT
[DF_Contacts_ID] DEFAULT newid(),
[FirstName] [nvarchar](50),
[LastName] [nvarchar](50),
CONSTRAINT [PK_Contacts] PRIMARY KEY
(
[ID]
)
)
If you were to create the table, it would look like this:
but, again, you don't have to physically create the table in SQL Server to create the script.
SMO Capture Mode
Another way to pull this off is to use SMO Capture Mode. Check out my following tutorial if you are interested:
Conclusion
SQL Server Management Objects has a lot of really cool features for working with SQL Server. In this SMO tutorial, I showed how to generate scripts for an in-memory table that never existed in a database. The ability to work with databases, tables, stored procedures, etc. that only exist in memory opens up the opportunity for some neat applications.
Source: David Hayden ( .NET Developer )
Filed: SMO Tutorials