Using SMO to generate stored proc script – by your design [Republished]


Using this sample, you can create a C# based utility which creates stored proc text using some of the nifty new features in SQL Server 2005.
 

This is a little tutorial I posted on my original code blog at http://code.blogdrive.com.  I have rewritten most of the original post.
 
It’s so simple to create SQL Server utilities from .Net nowadays, even more so than with SMO’s predecessor, DMO.  I’ve written half a dozen little utilities to help with day-to-day operations, like preparing a script containing 24 hours worth of updates to Stored Procedures, and here’s how..

Prerequisites: Upgrade to SQL Server 2005, install SQL Server 2005 SP1.  Visual Studio 2005. 
 
If you are working on a separate development enevironment where SQL Server is not installed locally, you’ll do well to install the developer tools locally in your development environment, and also potentially, SQL Management Studio.
 
Start a new C# (.Net 2.0) project.

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).

In the main() function, declare some Database and Server variables like so:

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)
{

    // Ignore System SPs
    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.

 
The next line to consider populates the Scripter with the SQL objects to script:

str = spr.Script(procedureList.ToArray());
Below is the definition of the WasCreated() method, which determines whether a Stored Prodecure should be included based on the last time it was modified, or when it was created.  Note: you may notice that I am comparing to a member variable, you can use your own discretion here.

static bool WasCreated(StoredProcedure sp)
{
    TimeSpan ts = DateTime.Now – sp.CreateDate;

    if (ts.Days < _agecomparison)
    {
        return true;
    }
    return false;
}

 
It is now safe to cleanup and exit your application.  If all has gone well, you should have a SQL Script (in the location specified to the FileName option, as set above) containing T-SQL for any stored procedures created or modified within the range set as _agecomparison.  Obviously you may modify this to your own requirements.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>