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:
You can download the source from skydrive:
8 comments:
Very helpful post! This was a big help to me and I am so glad that I found it. Thank you so much!
Tim
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
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;
}
Does anyone still have the sample code? The download link is dead.
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?
ScriptWithList() writes to the output file specified.
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...
@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 :)
Post a Comment