通过

SQL Server: SMO Scripting Basics

  • 项目

Let's first clarify what I mean by "scripting". It often happens during talks or when I explain SMO fundamentals that eyes gloss over, shortly after which the "what * do you mean by scripting" question pops up (replace the * with your favorite combination of adjective and noun to indicate bewilderment). For people who, unlike me, do not live their lives in SQL land, "scripting" means typically some kind of language script, like perl, VBScript, or others, that allows you, without the use of a development environment to author and execute code". Not so in this case. If I were to be precise, it means "serialize a SMO or DMO object into Transact-SQL". So now we have this out of the way, let's take a look at SMO Scripting.

 

SMO objects, when either just instantiated and not yet persisted, or just retrieved from the server, carry state. For example a StoredProcedure object has a name, a schema, a text body, and maybe some other optional properties. This state can be serialized into a Transact-SQL script that you may store, modify, or execute. As SQL Server’s primary language is Transact-SQL, it’s obvious that one of the tasks that the SMO object model has to perform very well is scripting. SMO objects can be scripted in 5 different ways:

 

  1. By calling the Script() method on the object.
  2. By instantiating a Scripter object, and pass in a reference of the object(s) to be scripted.
  3. Advanced scripting: generating a script in 3 distinct phases (discovery, list, script)
  4. By using the Transfer object.
  5. Indirectly, by capturing the SQL output of the objects

 

I will discuss 1 and 2 and will dedicate some more posts on the other more advanced topics.

Basic Scripting

Let’s start with scripting an existing object, with no options specified (installing AdventureWorks will help running these samples):

 

 Server svr = new Server();foreach (string s in svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"].Script()){      Console.WriteLine(s);}

If you run this code, the script should be emitted to the console. So far pretty straightforward eh?

Scripting Options

If you want to do more than just scripting the object, there are scripting options that you can pass into the Script() method. There 2 ways to pass in Scripting options. The following 2 samples illustrate the different methods of passing in scripting options.

 Server svr = new Server();
 StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];
  
 ScriptingOptions so = new ScriptingOptions();
 so.IncludeHeaders = true;
 so.SchemaQualify = true;
             
 foreach (string s in sp.Script(so))
 {
       Console.WriteLine(s);
 }

In the next sample, you use the ScriptOption class. This class has various static members that each return an instance of a ScriptionOption class.

 Server svr = new Server();
 StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];
  
 foreach (string s in sp.Script(ScriptOption.IncludeHeaders + ScriptOption.SchemaQualify))
 {
       Console.WriteLine(s);
 }

You can take advantage of the fact that the ScriptOption static members returns a ScriptingOption class (through implict conversion), as you can use it as an alternative to construct a Scriptingoption class:

 ScriptingOptions so = ScriptOption.IncludeHeaders;
 so.SchemaQualify = true;

Script modified objects

You can modify and script an object, without persisting its state. This sample shows how this can be accomplished:

 

 Server svr = new Server();StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];sp.TextHeader = "-- Scripted at " + DateTime.Now.ToString() + "\n\n" + sp.TextHeader;foreach (string s in sp.Script()){      Console.WriteLine(s);}

 

Ensure that you either call the Refresh() method on the object once you are done, or persist the object by calling Alter(), as this temporary state may cause problems when this object is referenced again for other purposes.

Script non-existing objects

Alternative, you can instantiate a new object, and emit the script for it, without requiring it to exist on the server. In fact the below script will not even connect to the server; this happens all on your client.

Server svr = new Server();Database db = new Database(svr, "MyDatabase");db.DatabaseOptions.AutoClose = true;foreach (string s in db.Script()){

      Console.WriteLine(s);}

Using the Scripter object

If you call the Script() method on an object, what actually happens, is that in the background a Scripter() object is instantiated, which performs the various scripting operations. Whereas each object knows how to emit script, the Scripter object pulls it all together, and does special processing before or while generating the output. Let’s take a look how the Scripter object can be used to accomplish the same task as above (scripting a database).

 Server svr = new Server();
  
 Database db = new Database(svr, "MyDatabase");
 db.DatabaseOptions.AutoClose = true;
  
 Scripter scripter = new Scripter();
 scripter.Server = svr;
 scripter.Options.IncludeHeaders = true;
 scripter.Options.SchemaQualify = true;
             
 SqlSmoObject[] objs = new SqlSmoObject[1];
 objs[0] = db;
  
 scripter.Script(objs);
  
 foreach (string s in scripter.Script(objs))
 {
       Console.WriteLine(s);
 }

The above samples I have touched on the basis of the scripting operations. In the next posts I will elaborate and show you more complex and elaborate ways to generate script. We made scripting extremely flexible, and hope that this first post helps you to get started with scripting.

Enjoy!

Comments

  • Anonymous
    May 07, 2005
    SMO Scripting Basics

  • Anonymous
    November 30, 2005
    Hi dude
    Thanks for the nice article.it is very informative.
    BTW i've one question is it possible to use SMO to replicate "Query Designer" programmatically?
    i want to write a query builder for end users and am wondering which objects in SMO i need to use to achieve what i want.
    You seem to have good understanding of these things and any help on this one much appreciated.
    Thanks

  • Anonymous
    February 28, 2006
    Great, I've gotten my program to script out DBs, tables, and data.  Now I need to know how to run these scripts on a different server at a different location programatically using SMO.  In SQL-DMO it is simple, using the ExecuteImmediate function of the Server object.  Can't figure out how to do this using SMO.  Appreciate any help.  You can E-mail me at schoudhry@gentax.com.

  • Anonymous
    March 01, 2006
    SMO (SQL Management Objects) in SQL Server 2005 replaces SQL-DMO that was present in the earlier...

  • Anonymous
    March 05, 2006
    I've had a couple problems with SMO and I wonder if anyone else has.  Specifically, when I run through stored procedures (just like your example) it is painfully slow.  Maybe 5 a second.  Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.

    Both cases crash my app.  Any ideas?

  • Anonymous
    April 24, 2006
    How can I script constraints only? (FK, default, etc) so when I do a large data import on multiple tables, I can drop constraints at the beggining, and re-create them at the end.

    Thanks!

  • Anonymous
    April 25, 2006
    I am hiding in shame that I did not see your comments... I should have been notified somehow and that did not happen. If your question is still unanswered, please feel free to drop me an email. My email is Michiel.Wories@(the rest is obvious).

  • Anonymous
    July 20, 2006
    nice. got me started off reading up on SMO.
    Thank you.

  • Anonymous
    November 27, 2006
    <a href=http://akoc-icco.ru/wholesale-nursery>wholesale nursery</a>

  • Anonymous
    December 01, 2006
    <a href= http://forum.lixium.fr/cgi-bin/index.eur?mitsu > wellbutrin sr </a> [url= http://forum.lixium.fr/cgi-bin/index.eur?mitsu ] wellbutrin medication [/url]

  • Anonymous
    December 08, 2006
    Very good project! <a href= www.debtconsolidation.newov.info >debt consolidation</a> <a href= www.badcreditloan.newov.info >bad credit loan</a>

  • Anonymous
    December 11, 2006
    Well, your site is very good, good news and design...! ))) Happy Christmas! SmilerMan.

  • Anonymous
    December 14, 2006
    Very nice resources!<a href= http://betting.ggfix.com >betting</a> <a href= http://wagering.ggfix.com >wagering</a>

  • Anonymous
    December 18, 2006
    I liked this site, it's neat. Good job! Would you please also visit my homepage? <a href=  ></a> [url=][/url]

  • Anonymous
    January 12, 2007
    Hello people! Nice site! <a href='http://hometown.aol.com/videochatbox'>videochatbox</a>

  • Anonymous
    January 19, 2007
    Amazing artwork! This is spectacularly done! Please visit my homepage: <a href= http://fm7.biz/0l6s >buy valium</a> [url=http://fm7.biz/0l6s]buy valium[/url]

  • Anonymous
    January 24, 2007
    Thank you! http://symy.jp/?Ct_220745,Thank">http://symy.jp/?Ct_220745,Thank you! http://symy.jp/?Ct_220745

  • Anonymous
    February 04, 2007
    This is my site: http://shurl.net/2SU ,This is my site: http://shurl.net/2SU

  • Anonymous
    February 16, 2007
    Hello! Great site!,Hello! Great site!

  • Anonymous
    February 16, 2007
    Hello! Great site!,Hello! Great site!

  • Anonymous
    March 24, 2007
    Hello! Hello and congratulations! Very interesting & professional site! DS2_sp_1

  • Anonymous
    March 24, 2007
    Hello! Thanx thanx thanx for such great job done above your website! DS2_sp_3

  • Anonymous
    March 24, 2007
    Hello! That's friggin' awesome. Thanks to all those involved DS2_sp_2

  • Anonymous
    March 30, 2007
    Very nice! I have some LJ with news, check this out: <a href= http://iwantubadlyz.livejournal.com >Newest news</a> <a href= http://annakubat.livejournal.com >Check this out</a> <a href= http://jackie_simpson.livejournal.com >livejournal</a>

  • Anonymous
    April 13, 2007
    Hello, nice site look this: http://jewl.info/salt-crystal-lamp-in-usa http://jewl.info/salt-crystal-lamp-in-usa http://jewl.info/salt-crystal-lamp-in-usa AST_spmt3

  • Anonymous
    April 18, 2007
    The Best Catalog. <a href=http://healthpiece.info/>Real">http://healthpiece.info/>Real Catalog</a>[url=http://healthpiece.info/]The Real Catalog[/url]

  • Anonymous
    April 30, 2007
    <a href= http://dietary-supplements.piercing-shop.biz >dietary</a>

  • Anonymous
    May 03, 2007
    Good Work dude! I will visit your website again.

  • Anonymous
    May 04, 2007
    Frankly, the way things are right now, I'm not sure I'd want to play myself in my very own movie of the week.

  • Anonymous
    May 07, 2007
    Good Work dude! I will visit your website again.

  • Anonymous
    May 10, 2007
    The comment has been removed

  • Anonymous
    May 10, 2007
    The comment has been removed

  • Anonymous
    May 10, 2007
    The comment has been removed

  • Anonymous
    May 10, 2007
    The comment has been removed

  • Anonymous
    May 17, 2007
    Very good website you have here, After the visit I put my step in to your guestbook. http://valium1.blogcu.com/ Buy Valium

  • Anonymous
    May 18, 2007
    What a good site! I think it wasnt easy to post here so much information. Thank you, I will add it to my bookmarks

  • Anonymous
    May 19, 2007
    What a good site! I think it wasnt easy to post here so much information. Thank you, I will add it to my bookmarks

  • Anonymous
    May 20, 2007
    I found lots of intresting things here. Please more updates.

  • Anonymous
    May 28, 2007
    Thanks for taking the time and effort in creating this content to share your knowledge with all of us.

  • Anonymous
    May 28, 2007
    Site - very comprehensive and meticulous from all sides, its good! Just excellent website, I sure!

  • Anonymous
    June 02, 2007
    You have very nice site! http://campingwaterheaters.iespana.es http://www.volny.cz/coachshoesitaly/ http://idisk.mac.com/louisvuittonsunglass/Public/index.html http://amping-water-heaters.blogspot.com

  • Anonymous
    June 02, 2007
    Hey! This is really your Work?! Cool! I never earlier did not see sites like this! Tnx!

  • Anonymous
    June 04, 2007
    Site - very comprehensive and meticulous from all sides, its good! Just excellent website, I sure!

  • Anonymous
    June 04, 2007
    Hi Guys! What Your Site Powered By?

  • Anonymous
    June 06, 2007
    This is really fresh idea of the design of the site! I seldom met such in Internet. Good Work dude!

  • Anonymous
    June 09, 2007
    nice site! http://idisk.mac.com/dtd4/Public/ http://pumacartshoes.iespana.es

  • Anonymous
    June 26, 2007
    The comment has been removed

  • Anonymous
    July 02, 2007
    You have very interesting site! Respect you! http://louisellipsehandbag.iespana.es

  • Anonymous
    August 01, 2007
    Hi fellows, i'm a newbie on your blog and maybe the first time ever that I participate at an online blog; anyway, there's always a first time... Your short article on basic scripting is quite nice and straight forward - good job - but there's something i don't get right: lots of questions asked but why so few answers?? Somebody was concerned with slow running scripting and I had the same experience; i just corrupted one of my testing SQL Server 2K databases (not really serious but...) while attaching it to an instance of SQL Server 2K5. I just wanted to recover it (the structure and later its contents) through SMO and noticed that scripting process was unbelievable slow (1h 50mn for about 1400 objects [including system objects]) compared to the way SQL Server 2K does the same job... Any idea off what is wrong or being misused? Quick response would be very appreciated. Thanx in advance. Abdoul. PS: If you'd like I can send u the source code.

  • Anonymous
    March 06, 2008
    Welcome to <a href="http://real-estate-cblog.info/172.html ">marblehead real estate</a> <a href="http://real-estate-cblog.info/853.html ">real estate adirondacks</a> <a href="http://real-estate-cblog.info/330.html ">montelongo real estate</a> <a href="http://real-estate-cblog.info/91.html ">loudoun county real estate</a> [URL=http://real-estate-cblog.info/82.html]los altos real estate agents[/URL] [URL=http://real-estate-cblog.info/459.html]newark de homes for sale[/URL] more

  • Anonymous
    March 08, 2008
    Do you like it? <a href="http://credit-cards-info-info.info/312.html ">credit card holder metal accordian</a> <a href="http://credit-cards-info-info.info/417.html ">citifinancial credit card</a> <a href="http://credit-cards-info-info.info/586.html ">sainsburys credit card lost</a> <a href="http://credit-cards-info-info.info/639.html ">acept credit cardc</a> [URL=http://credit-cards-info-info.info/204.html]fd100 credit card printer[/URL] [URL=http://credit-cards-info-info.info/359.html]credit card charge paid consult[/URL] next

  • Anonymous
    June 15, 2008
    Of course, but what do you think about that?,