How to Verify Your Stored Procedures

Posted in: Programming by Steve on May 5, 2010

One of the things I’ve been trying to do is to eliminate the unnecessary stored procedures from our project.  Currently, we have about 150 left, but a lot of them are still used.  Some of them are still left over and no longer are called from our application.  As I was building a new SQL install script, I was trying to run a script of all stored procedures in one database and create them all in a new one.  I found that some of them no longer would work because of changes we’ve made to the database that have broken these stored procedures without even knowing. 

I asked on twitter how this could be done and Argenis Fernandez (@afernandez) suggested that I look into a Powershell script to verify stored procedures still work.

After some investigation on how Powershell and SQL work together, I scraped together this script:

$server = "localhost";		# The SQL Server instance name
$database = "MyDatabase";		# The database name

# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

# Create the SMO objects
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");

# Get the database
$db = $srv.Databases[$database];

# For each stored procedure in the database
foreach($proc in $db.StoredProcedures)
{
    # NOTE: my stored procedures are all prefixed with "web_"
    if ($proc.Name.StartsWith("web_"))
	{
        $proc.TextBody = $proc.TextBody;
        $proc.Alter();
    }
}

If any error occurs, it will display which procedure name did not get altered properly.

In my application, all my stored procedures that I use in the websites are prefixed with “web_”.  This made it easy to check if the procedures used still work.