THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Ben Miller

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.

 

Published Wednesday, October 03, 2007 6:59 AM by dbaduck
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hugo Kornelis said:

Hi Ben,

Do you happen to be the same Ben Miller I met during the 2006 PASS Summit in Seattle, and once more in March 2007, again in Seattle? Or are you someone else with the same name?

Anyway, whether you are him or someone else - welcome to the blogosphere and welcome to SQLBlog.com!

October 4, 2007 2:41 AM
 

dbaduck said:

I am the same Ben Miller.  I was an MVP Lead for Microsoft, so yes this is me.  Thanks for the Welcome and it is a privilege to be among you all.

October 4, 2007 6:20 AM
 

andyleonard said:

Welcome Ben!

  This is good stuff! I missed Allen's presentation but I'm ordering the DVD.

  Good to meet you at PASS.

:{> Andy

October 4, 2007 10:53 AM
 

dbaduck said:

Thanks Andy. It was great to meet you too, and see you around.

Ben.

October 4, 2007 11:15 PM
 

Ben Miller said:

In my previous post I illustrated scripting tables out with SMO in .NET. I have tested a new version

October 15, 2007 10:50 AM
 

burak güven said:

hi, i got a school project about kind of red gate program. i've to compare two databases(source and destination) at first, then if there are different tables in my source, i must create the same table in the dest.  I was wondering about smo and this is the opportunity for me. I did what you have explained here. but if i want to take scripts from the file i created or directly from stringbuilder object how should i use that script?  i mean i can get the scripts s you showed above, but if you can give me some advices about using the file created, i'd be grateful. thanx for the articles mr. miller. This was very useful to me. have nice day.

November 7, 2007 4:54 PM
 

Manish Kaushik said:

Hi Ben,

Thanks a lot for the wonderful post.

But i am facing a problem here after executing the file i.e. output.txt.

I know its because the child table are created first and it seek for the parent table , where as the script for the parent table are some where later in the script.

I tried by "options.WithDependencies=true" but still no luck.

Please advice how can I manage.

Thank

Manish

October 25, 2008 9:33 AM
 

Dave Blair said:

Were there any updates (part 2 and beyond ) to this ?

March 17, 2011 5:53 PM
 

dbaduck said:

@Dave,

Yes there is a part 2 and 3 and beyond.

http://sqlblog.com/blogs/ben_miller/archive/2007/10/15/table-scripting-with-smo-part-2-with-powershell.aspx

Let me know if you have questions.

March 17, 2011 6:04 PM
 

Robert said:

Hi Ben

This is a good article, but I have one question. This works great when you have a few tables in DB, what if you have some hundreds of tables? Is there any way how to improve performance? Currently am working on program for DB compare and I need to script DB objects. I tried to get urn of all objects and than pass it to Scripter object. It was a little bit quicker than script every object separately, but not as I expected. Second problem is, when I get script, I must split it.

Regards

Robert

May 20, 2011 7:36 AM
 

dbaduck said:

Robert,

When you say split, do you mean into individual files? That is the way it is done above.  I have actually used the method you say in a new project.  I gather all the URNs and pass it into a scripter.  

In this new project, I learned about scripting out in batches and also to turn off the string output and script to just a file only and did notice that there is a pretty good increase in speed, if it is a bigger database.

Option: BatchSize

Option: ToFileOnly

Now splitting it out, that will probably be the time killer in deciding whether to go to the Scripter with URNs or individual objects, because the effort and time to split them out to individual files, will outweigh just doing these individually.

Ben.

May 20, 2011 9:04 AM

Leave a Comment

(required) 
(required) 
Submit

About dbaduck

Ben Miller is a Senior Database Administrator for HealthEquity in Draper, UT. He has been working with SQL since SQL Server 6.0 (1998) and has had a variety of roles in his career, including SQL Support and MVP Lead at Microsoft.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement