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 (52)

  1. SMO Scripting Basics

  2. Rajani says:

    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

  3. Shahzad Choudhry says:

    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.

  4. SMO (SQL Management Objects) in SQL Server 2005 replaces SQL-DMO that was present in the earlier…

  5. 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?

  6. ader says:

    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!

  7. mwories says:

    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).

  8. karthik says:

    nice. got me started off reading up on SMO.

    Thank you.

  9. Besid,Besid says:

    Very good project! <a href= http://www.debtconsolidation.newov.info >debt consolidation</a> <a href= http://www.badcreditloan.newov.info >bad credit loan</a>

  10. Tonik says:

    Well, your site is very good, good news and design…! ))) Happy Christmas! SmilerMan.

  11. Ocis,Ocis says:

    Very nice resources!<a href= http://betting.ggfix.com >betting</a> <a href= http://wagering.ggfix.com >wagering</a>

  12. Cathy says:

    I liked this site, it’s neat. Good job! Would you please also visit my homepage?

    <a href=  ></a> [url=][/url]

  13. videochatbox says:

    Hello people! Nice site! <a href=’http://hometown.aol.com/videochatbox‘>videochatbox</a>

  14. Hairston says:

    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]

  15. Hello! Great site!,Hello! Great site!

  16. Hello! Great site!,Hello! Great site!

  17. eoxlente says:

    Hello!

    Hello and congratulations! Very interesting & professional site!

    DS2_sp_1

  18. grimucdo says:

    Hello!

    Thanx thanx thanx for such great job done above your website!

    DS2_sp_3

  19. tovnarcy says:

    Hello!

    That’s friggin’ awesome. Thanks to all those involved

    DS2_sp_2

  20. Britneydqiuo says:

    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>

  21. The Best Catalog.

    <a href=http://healthpiece.info/>Real”>http://healthpiece.info/>Real Catalog</a>[url=http://healthpiece.info/]The Real Catalog[/url]

  22. Good Work dude! I will visit your website again.

  23. 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.

  24. Good Work dude! I will visit your website again.

  25. Buy Valium says:

    Very good website you have here, After the visit I put my step in to your guestbook.

    http://valium1.blogcu.com/ Buy Valium

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

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

  28. Buy Caverta says:

    I found lots of intresting things here. Please more updates.

  29. Buy Caverta says:

    Thanks for taking the time and effort in creating this content to share your knowledge with all of us.

  30. Buy Caverta says:

    Site – very comprehensive and meticulous from all sides, its good! Just excellent website, I sure!

  31. Hey! This is really your Work?! Cool! I never earlier did not see sites like this! Tnx!

  32. Buy Caverta says:

    Site – very comprehensive and meticulous from all sides, its good! Just excellent website, I sure!

  33. Buy Caverta says:

    Hi Guys! What Your Site Powered By?

  34. This is really fresh idea of the design of the site! I seldom met such in Internet. Good Work dude!

  35. Bob says:

    adidas trail running shoes

    http://www.volny.cz/adidas/

    louis vuitton cherry blossom handbags

    http://www.volny.cz/luis2007/

  36. Ron says:

    You have very interesting site!

    Respect you!

    http://louisellipsehandbag.iespana.es

  37. kababdoul says:

    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.

  38. 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

  39. 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

  40. Iyzreajy says:

    Of course, but what do you think about that?,