I’ve created little utility functions for over 10 years now and I can honestly say that I don’t have a single one saved off somewhere to look back to. Actually, since I wrote one, they’ve changed the objects, methods, properties, etc. so much that it wouldn’t matter. So I’m dorking with scripting a little bit, and I’ve always enjoyed it.
Since I am doing more DBA type stuff these days, I want a .exe to script databases on a number of servers daily and save the files off. I always like to have files handy just in case. Here’s one that I wrote today; I’ll be posting a few more of these as I play around with them and make them more robust. My idea comes from the fact that when you script in SQL Server 2008 R2, you can’t save the wizard screens! And, because we have encrypted stored procedures, I have to remember which ones to exclude because if you don’t uncheck and leave those out, the script wizard bombs out. Here’s a little code that you can try or make better.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Collections.Specialized;
namespace Script
{
class GenerateSQLScript
{
private static ScriptingOptions Options()
{
ScriptingOptions options = new ScriptingOptions();
options.ScriptBatchTerminator = true;
options.Triggers = true;
options.PrimaryObject = true;
options.IncludeHeaders = false;
options.ScriptData = false;
options.IncludeIfNotExists = false;
options.ScriptSchema = true;
options.ToFileOnly = true;
options.AppendToFile = true;
options.WithDependencies = false;
options.ScriptDrops = false;
options.FileName = @"\\" + Environment.MachineName + "\\c$\\temp\\Script_"
+ string.Format("{0:yyyyMMdd}", DateTime.Now) + ".txt";
return options;
}
public static void Main(string[] args)
{
Server srv = new Server();
Database db = srv.Databases["AdventureWorksDW2008R2"];
Scripter scrp = new Scripter(srv);
scrp.Options = Options();
foreach (ScriptSchemaObjectBase Type in db.StoredProcedures)
if (!((StoredProcedure)Type).IsSystemObject
&& !((StoredProcedure)Type).IsEncrypted)
scriptObject(Type, scrp);
foreach (ScriptSchemaObjectBase Type in db.UserDefinedFunctions)
if (!((UserDefinedFunction)Type).IsSystemObject
&& !((UserDefinedFunction)Type).IsEncrypted)
scriptObject(Type, scrp);
foreach (ScriptSchemaObjectBase Type in db.Tables)
if (!((Table)Type).IsSystemObject)
scriptObject(Type, scrp);
foreach (ScriptSchemaObjectBase Type in db.Views)
if (!((View)Type).IsSystemObject)
scriptObject(Type, scrp);
}
static void scriptObject(ScriptSchemaObjectBase Type, Scripter scrp)
{
Urn[] smoObjects = new Urn[1];
smoObjects = new Urn[1];
smoObjects[0] = Type.Urn;
scrp.EnumScript(smoObjects);
}
}
}