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:
- By calling the Script() method on the object.
- By instantiating a Scripter object, and pass in a reference of the object(s) to be scripted.
- Advanced scripting: generating a script in 3 distinct phases (discovery, list, script)
- By using the Transfer object.
- 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.
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!
