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)
Published Thursday, October 18, 2007 10:34 AM by dbaduck



andyleonard said:

Hi Ben,

  That dependency tree code is nice - thanks for sharing!

:{> Andy

October 18, 2007 12:27 PM

rnm said:

"If you have any ideas on the Powershell Add-Content method, and getting Unicode instead of UTF8 out of it, I am all ears. "

how about out-file with its -encoding parameter?

February 6, 2008 1:26 PM

Manish said:

I am unable to work with ExecuteWithResults command. it gives me an exception 'Execute with result failed for Database'



October 26, 2008 3:55 AM

ThreadKiller said:

Just in case anyone else is getting the 'Execute with result failed for Database' error that Manish and I both received, be sure to check the following:

1. You have a stored procedure in your master database on the server called sp_generate_inserts.  If you do not, you can find the sp here: (or maybe in earlier parts of this tutorial?)

2. Be sure to exclude system tables from your insert script generation.  I accomplished this simply with the addition of the following code:

     foreach (DependencyCollectionNode dep in depcoll)


       if (database.Tables[dep.Urn.GetAttribute("Name")].IsSystemObject)


       insertSql.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);


June 3, 2009 11:55 AM

Eric said:

Hi, nice but doesn't work with SQL2005, do you have the equivalent script


October 5, 2009 8:41 AM

sydney said:

Very nice sample!  I modified a bit to suit my needs and it's working fine.  However, I can't seemed to figure it out how to include the DROP statement at the top of each procedure in the script.  I added two more options in addition to what you have and they are:

options.IncludeIfNotExists = True

options.ScriptDrops = True

However, the output script ONLY contains the IF EXIST and DROP statements as in:

/****** Object:  StoredProcedure [dbo].[proc_Test]    Script Date: 10/16/2009 10:27:44 ******/

DROP PROCEDURE [dbo].[proc_Test]


The actual body of the proc was not included.  I played around with

some of the option settings but couldn't figure out how to include it.  How do you include the body?

Thank you !

October 16, 2009 10:32 AM

Sydney said:

I have figured it out how to include the body after the IF EXISTS and DROP at the top.  Basically, after you script out the IF EXISTS and DROP, you need to turn off these options and script it once more as in:

options.AppendToFile = True

options.IncludeIfNotExists = False

options.ScriptDrops = False

scr.Options = options


October 16, 2009 10:40 AM

khushbu said:

i also get that error "Execute with result failed for Database'"

Please help me

February 1, 2012 3:04 AM

dbaduck said:

khushbu, look at ThreadKillers response above and try that as you may be missing the procedure that generates the inserts.


February 2, 2012 8:56 PM

rohit garg said:

I have used your script and successfully generated the database schema. The problem i am facing is that at insert time, the script generated by your code fails as it tries to insert data in primary key columns too. And i also need to create stored procedures too. how to do that. Reply urgently

April 15, 2013 1:31 PM

Sunil Sain said:

this is really helpful...but how we can generate only insert statement do not want to generate create or alter process.Only data schema

August 27, 2014 6:28 AM
New Comments to this post are disabled

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.
Privacy Statement