THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
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);
            scr.Script(tbls);
            
            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];
                    scr.Script(view);
                }
            }

            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)
                {
                    sw.WriteLine(dr[0].ToString());
                }
            }
            sw.Close();

Published Thursday, October 18, 2007 10:34 AM by dbaduck

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

 

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

Leave a Comment

(required) 
(optional)
(required) 
Submit

About dbaduck

Ben Miller is the Director of Database Administration for Nature's Sunshine Products in Provo, UT. He has been working with SQL since SQL Server 6.0 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