Table scripting with SMO (PART 1)

Thanks to Allen White and his talk at PASS, I have been inspired in my plight to create a complete build script of the database that I am in charge of for a dev project at work.  So I wanted to start by scripting the tables that exist in my database.  I was going to have it be configurable but that is later on.  My end product will actually derive the Foreign Keys and script the PK tables first so that the FK tables do not load first, and also it will create the INSERT statements for all the data that is in the database... So stay tuned for the rest of the parts.

So goes PART 1:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using Microsoft.SqlServer.Management.Smo.CoreEnum;
using System.Configuration;
using System.Collections.Specialized;

namespace Application_Build
{
    class Program
    {
        static void Main(string[] args)
        {
            string dbName = ConfigurationManager.AppSettings["databaseName"];
            
            Server srv = new Server();
            srv.ConnectionContext.LoginSecure = false;
            srv.ConnectionContext.Login = ConfigurationManager.AppSettings["loginName"];
            srv.ConnectionContext.Password = ConfigurationManager.AppSettings["loginPassword"];
            srv.ConnectionContext.ServerInstance = ConfigurationManager.AppSettings["serverName"];
            
            Database db = new Database();
            db = srv.Databases[dbName];
            
            StringBuilder sb = new StringBuilder();

            foreach(Table tbl in db.Tables)
            {
                ScriptingOptions options = new ScriptingOptions();
                options.ClusteredIndexes = true;
                options.Default = true;
                options.DriAll = true;
                options.Indexes = true;
                options.IncludeHeaders = true;

                StringCollection coll = tbl.Script(options);
                foreach (string str in coll)
                {
                    sb.Append(str);
                    sb.Append(Environment.NewLine);
                }
            }
            System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
            fs.Write(sb.ToString());
            fs.Close();
        }
    }
}

With the app.config for the config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="loginName" value="sqladmin"/>
<add key="loginPassword" value="sql123ms"/>
<add key="serverName" value="(local)\SQL"/>
<add key="databaseName" value="INFOGUIDE" />
</appSettings>
</configuration>

This simply uses the Table object and scripts to a StringBuilder so that I can do something with the text afterwards. I know that there are many other ways of doing this, but this is a learning process to learn a new thing. SMO scripting is a very powerful thing and I think I will translate this to Powershell since it is the new thing to use in scripting.  Allen did part of his session on Powershell as well.

Stay tuned for Part 2... where I will include a Powershell version and also add in the ordering of it by FK-PK relationships to script them in the right order.