Professional SQL Server™ 2005 Programming

25.6. Scripting

Perhaps one of the most compelling abilities that SMO offers the true developer crown is theability to script out objects that are already in the database. Indeed, SMO can script out backups, reverse engineer tables, and even record the statements being sent to the server.

For our example, we're going to reverse engineer a script for the HumanResources.Employee table in the AdventureWorks database. We'll see just how easily even a relatively complex table definition can be scripted out for other use.

We start with the same server, connection, and database reference code we've used several times in this chapter:

private void btnScript_Click(object sender, EventArgs e)
{
    // Create the server and connect to it.
    ServerConnection cn = new ServerConnection();
    cn.LoginSecure = true;

    Server svr = new Server(cn);
    svr.ConnectionContext.Connect();

   // Now define the database we want to reference the table from.
   Database db = svr.Databases["AdventureWorks"];

Next, we set a reference to the table that we want to script out — we could just as easily be scripting out a different type of SQL Server object such as a stored procedure, a view, or even a database. Indeed, it can even be a server-level object such as a device or login.

// Get a reference to the table. Notice that schema is actually the
*2nd* parameter
// not the first.
Table Employee = db.Tables["Employee", "HumanResources"];

We're then ready to call the Script() method. The only real trick here is to realize that it ...