Generate a Table Create Script using SQL Server Management Objects
by David Hayden ( Florida .NET Developer )
I have been posting a lot about SQL Server Management Objects these days, because this is new and cool stuff to me. I had never played with SQL-DMO in the past, so I am absolutely amazed at all the functionality and features available in SQL Server Management Objects to help create tables, stored procedures, transfer data, etc. in SQL Server. Now I realize how Red Gate is able to create such wonderful SQL Server Tools to help me with all the hastle of packaging / copying databases and synchronizing the schema and data in databases.
To date, I have written two posts on SQL Server Management Objects discussing how to create stored procedures and tables using SMO:
This is really cool stuff IMHO.
I have also learned how to create scripts for various objects in SQL Server. For example, if you want to generate a create script for a particular table in a SQL Server Database it is as simple as follows:
Server server = new Server(".");
Database northwind = server.Databases["Northwind"];
Table categories = northwind.Tables["Categories"];
StringCollection script = categories.Script();
string[] scriptArray = new string[script.Count];
script.CopyTo(scriptArray, 0);
Above creates a script to re-create the Categories Table in the Northwind Database for the local instance of your SQL Server Database.
When you call the script method on any object in a database, it generates a creation script for an object. It places this script in a StringCollection object for you to do with as your please. In the code above I am just copying it to a string array so I can easily show it to you in a debugger visualizer as below:
Now you see how a lot of SQL Server utlities and applications are able to pull off a lot of code generation as well as help you manage SQL Server. This is only the tip of the iceberg, however, as I will show you how to transfer complete databases, etc. using more functionality available in SQL Server Management Objects.
Source: David Hayden ( Florida .NET Developer )
Free SQL Server Tutorials