Let’s assume you’ve created a C# console app. Add the appropriate (minimal) .NET Project References (Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoEnum and Microsoft.SqlServer.ConnectionInfo).
In code, add the appropriate using (import) directives as so:
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Generic;
Now, connecting to SQL Server is so easy using SMO (depending on your setup).
Server _Server;
Database _Database;
string server = "server"; // The SQL named instance
_Server = new Server(server);
string db = "database"; // Name of the database to connect to
_Database = _Server.Databases[db];
This sets up the objects you need – including authentication (the default is Windows Authentication).
You may need additional authentication, but that is outside the scope of this post.
Using generics, create storage for all Stored Procs we want to include
List<SqlSmoObject> procedureList = new List<SqlSmoObject>();
foreach (StoredProcedure sp in _Database.StoredProcedures)
{
if (!sp.IsSystemObject)
{
if (WasCreated(sp))
{
procedureList.Add(sp);
}
}
}
The WasCreated() function is examined in detail below. It is used to filter the Stored Procedures, so that only new or modified Procs are included.
Next, create the Scripter object. This is a new object in SQL Server 2005 which marks up SQL Database objects into appropriate T-SQL, given various options and depending on the type of objects you use it with.
Scripter spr = new Scripter(_Server);
//Write the Create Statements
spr.Options.Permissions = true;
spr.Options.ScriptDrops = false;
spr.Options.FileName = "path and filename";
Hopefully the various options make sense. I’ve used Permissions so that the Scripter will include the assignment of Roles to the objects, as well as scripting the Drop (if exists) statements for neatness. FileName refers to the output filename (and location) to store the final SQL script.
static bool WasCreated(StoredProcedure sp)
{
TimeSpan ts = DateTime.Now – sp.CreateDate;
if (ts.Days < _agecomparison)
{
return true;
}
return false;
}
