THE SQL Server Blog Spot on the Web

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

Ben Miller

  • Scripting Tables, Views and Data using SMO (part 3)

    Below is the final code that I used to script off my Tables, Views and the data using sp_generate_inserts from Vyas.  This is something that I have needed to do, and I found that using SMO for this is a great tool.  Mind you, that the data file that gets output to me is 22 MB from this and it runs within 2 seconds including putting out all the data to me in a file.  I did run into some interesting side affects in getting data back and trying to put it into a Text file.  I noticed that if I used any encoding except Unicode, that I got binary in my output file.  I have not figured it out in powershell yet, but in .NET and SMO I can use the Encoding.Unicode when I open the file to append to it. I know that I can do it in Powershell using .NET objects, but I want to use the Add-Content, but have not been able to make it work. 

    The nice trick that I found with this code is that I create a Dependency Tree (or at least Smo does), and then I script the tables data out in the order that it should so that Primary Keys exist before the tables with Foreign Keys to them exist, so it really is nice to script it all out.  I also have the additional code to do Stored Procedures, but we have not got there yet, but it is essentially like the Views except you use the Stored Procedure object.  If you have any ideas on the Powershell Add-Content method, and getting Unicode instead of UTF8 out of it, I am all ears.  Happy days to you and your SMO.

                string dbName = "DATABASENAME";
                string outputfile = @"c:\temp\output.sql";
                Server srv = new Server(@"(local)\INSTANCE");
                srv.ConnectionContext.LoginSecure = true;
                Database db = new Database();
                db = srv.Databases[dbName];
                Scripter scr = new Scripter(srv);
                srv.SetDefaultInitFields(typeof(View), "IsSystemObject");
                ScriptingOptions options = new ScriptingOptions();
                options.DriAll = true;
                options.ClusteredIndexes = true;
                options.Default = true;
                options.DriAll = true;
                options.Indexes = true;
                options.IncludeHeaders = true;
                options.AppendToFile = false;
                options.FileName = outputfile;
                options.ToFileOnly = true;
                scr.Options = options;
                Table[] tbls = new Table[db.Tables.Count];
                db.Tables.CopyTo(tbls, 0);
                options.AppendToFile = true;
                View[] view = new View[1];
                for (int idx = 0; idx < db.Views.Count; idx++)
                    if (!db.Views[idx].IsSystemObject)
                        view[0] = db.Views[idx];
                DependencyTree tree = scr.DiscoverDependencies(tbls, true);
                DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
                DependencyCollection depcoll = depwalker.WalkDependencies(tree);
                StreamWriter sw = new StreamWriter(outputfile, true, Encoding.Unicode);
                StringBuilder sb = new StringBuilder();
                foreach(DependencyCollectionNode dep in depcoll)
                    sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
                DataSet ds = new DataSet();
                ds = db.ExecuteWithResults(sb.ToString());
                foreach (DataTable dt in ds.Tables)
                    foreach (DataRow dr in dt.Rows)
  • Using a typeof() in Powershell for SMO server.SetDefaultInitFields

    In trying to squeeze performance out of the SMO stuff that I have been learning and doing for SQL Server I came across the method on the Server object called SetDefaultInitFields.  In my previous posts on Powershell or .NET and SMO, when you start scripting Views and Stored Procedures you start to get into areas where there are great amounts of System Objects.  In order to not script them out in the scripter, you either need to iterate over the collection or check IsSystemObject then script them, but either way you do it, you have to worry about the round trips to the server for more properties.

    By default SMO gives minimal properties back to you from the server when going after collections of objects.  The SetDefaultInitFields allows you to specify a Type of object and a string array of properties to put in the return by default so that round tripping is not necessary. (See Michiel Wories blog post).  The challenge for me was to figure out how to use typeof() (C#) in Powershell.  I found a couple of references to creating a PS Add in which would work, but I just needed it to pass a type so that the method would work.

    I found out in a forum post long ago (2006) that if you want to do a typeof(Microsoft.SqlServer.Management.SMO.StoredProcedure) then you would just do this :

    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")

    This line tells the server to return IsSystemObject as part of the properties that come back for the collection, which by default does not get returned, and when I query the property, it will get it from the object instead of asking the server about it, and it cut my execution time from 2 minutes to 10 seconds.  This is a great amount of savings when you have thousands of procedures and then you add in all the System Stored Procs, what a time killer and the SetDefaultInitFields is a time saver.

  • Table scripting with SMO (Part 2 with Powershell)

    In my previous post I illustrated scripting tables out with SMO in .NET.  I have tested a new version this time in Powershell.  I used the model with args, but you can just put it in the script as well.  This also goes and scripts out your Views as well, but only the ones that are non-system objects, because if you take off the WHERE in the bottom, you will get all the sys.* and INFORMATION_SCHEMA views as well.

    # scripttables.ps1
    # Lists space used by tables in database of your choice
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

    $server = $args[0]
    $dbname = $args[1]
    $output = "c:\temp\output.txt"

    $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
    $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
    $scr = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter")

    $db = $srv.Databases[$dbname]

    $scr.Server = $srv
    $options = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
    $options.DriAll = $True
    $options.ClusteredIndexes = $true
    $options.Default = $true
    $options.DriAll = $true
    $options.Indexes = $true
    $options.IncludeHeaders = $true
    $options.AppendToFile = $false
    $options.FileName = $output
    $options.ToFileOnly = $true

    $scr.Options = $options

    $options.AppendToFile = $true
    $views = $db.Views | where {$_.IsSystemObject -eq $false}


  • 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)
                System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
    With the app.config for the config:

    <?xml version="1.0" encoding="utf-8" ?>



    <add key="loginName" value="sqladmin"/>

    <add key="loginPassword" value="sql123ms"/>

    <add key="serverName" value="(local)\SQL"/>

    <add key="databaseName" value="INFOGUIDE" />



    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.


More Posts « Previous page
Privacy Statement