Google
 

Friday, November 14, 2008

Scripting SQL Server database objects using SMO

Scripting a database objects from development environment is a necessary step before deployment to production or test environments and to add the script to source control. Doing this manually is error prone and time consuming.
Also If you practice continuous integration, it's a must to automate the script generation process.

I used Database Publishing Wizard which is hosted on codeplex. It's good but the command line options lack the ability to determine which objects to script. That caused a problem because it scripted users as well. Which I did not want it to do.

Although it's hosted on codeplex, I could not download the source code to make the necessary modifications.

So, I had to go the hard way. And I did it myself. Using SQL Server Management Objects (SMO), I built a small command line utility to script: Tables, Indexes, UDFs, Veiws, SPs, Defaults, Checks, Foreign keys.
There are limitations ofcourse, But at least it's under control as long as the code is available.

The application depends on the core class ScriptingEngine, which in turn depends on Microsoft.SqlServer.Management.Smo.Scripter class.

Points of interest:
  • Order of scripting is important for the script to be used to regenerate objects.
  • Determining object dependencies is an important trick. Using DiscoverDependencies method, it's possible to order objects bu


public void ScriptViews()
{
List<urn> urns=new List<urn>();

foreach (View view in _database.Views)
{
if (view.IsSystemObject)
continue;
urns.Add(view.Urn);

}

DependencyTree tree = _scripter.DiscoverDependencies(urns.ToArray(), true);
DependencyCollection dc = _scripter.WalkDependencies(tree);

RemoveUrnType(dc,"Table");
RemoveUrnType(dc, "UserDefinedFunction");

_scripter.ScriptWithList(dc);

}

  • Note the RemoveUrnType method, it's used to filter unwanted object types from the DependencyCollection:


private void RemoveUrnType(DependencyCollection dc,string type)
{
for (int counter = 0; counter < dc.Count; counter++)
{
if (dc[counter].Urn.Type==type)
{
dc.RemoveAt(counter);
counter--;
}
}
}

  • Usage:
SqlScripter.exe -server:server\instance -database:dbname -login:login -password:pwd -filename:pathtoscriptfile

You can download the source from skydrive:

8 comments:

Anonymous said...

Very helpful post! This was a big help to me and I am so glad that I found it. Thank you so much!

Tim

Bharath said...

Really very helpful for the SMO begginers some one like me :-). I was struggling to understand the usage of Dependency walker. Thank you Hesham.

Cheers
Bharath

J Wynia said...

I don't know why this would be different than when you ran this on your machine, but on mine (Vista64), the args collection is clearly getting parsed differently.

Instead of the second half of each arg just *being* the second half, like your code implies it is on your side (i.e. for -server:SERVERNAME, SERVERNAME is a substring of the same arg as -server:), on my side, -server and SERVERNAME are 2 separate args.

Not a big deal to change, but thought you might want to know. Here's the updated contents of the SetParameter method:

private static void SetParameter(string name,string[] args,out string value)
{
name="-"+name+":";
for (var i = 0; i < args.Length;i++ )
{
String arg = args[i];
if (arg.StartsWith(name, StringComparison.CurrentCultureIgnoreCase))
{
value = args[i + 1];
return;
}
}
value = null;
}

Anonymous said...

Does anyone still have the sample code? The download link is dead.

Anonymous said...

Oops. Nevermind. :)
The sample doesn't have the source code.
The last line in the blog post is: _scripter.ScriptWithList(dc);

What picks up the output of the ScriptWithList() method?

Hesham A. Amin said...

ScriptWithList() writes to the output file specified.

Anonymous said...

I made a few upgrades to this. I stopped it duplicating dependencies. It also creates the bindings (like applying defaults to UDDTs). Also outputs schemas and schmea qualifies FK references. I can't see a way on this site to email it too you though...

Hesham A. Amin said...

@Anonymous:
It's great to see other developers extending this tool.
You can share your changes using your skydrive account (http://windowslive.com/online/skydrive). It's free. You just need a windows live id (hotmail account).
And don't forget to post the link :)