Alter Many Stored Procedures with Powershell at once

I was tasked to update any stored procs that referenced a particular table and substitute that table for a new one. So, here’s the query I used to figure out how many procs referenced that particular table… I got 136 hits!


USE MyDatabase;
GO

SELECT o.NAME
 ,sm.[object_id]
 ,sm.[definition]
 ,o.[type]
 ,o.type_desc
FROM sys.all_sql_modules sm
INNER JOIN sys.objects o ON o.[object_id] = sm.[object_id]
WHERE [o.type] = 'P' -- sql stored procedure
 AND sm.[definition] = '%particular_table%';

Um… so I got to thinking how to replace that particular table referenced in all those stored procs QUICKLY and correctly…  I got to thinking how to do this programmatically with t-sql, but then thought about using PowerShell.   In the past, I had read that powershell was very powerful for maintaining and administering SQL Server.  Thus began my journey into actually using powershell for the first time. So, I went home that night and began researching more about PowerShell.  As I read more,  I got more excited.

Powershell is built upon the .NET Framework, which means there’s an object-oriented framework to actually create scripting code that works on objects… to think in objects as opposed to the mindset of scripting one would find in a variety of unix flavors (no disrespect to Nemeth et al)  Anyway, back to the task at hand.

I came across this site, http://www.youdidwhatwithtsql.com/, and it contained a gem of a post for the problem I wanted to solve quickly using PowerShell!  The post is called ALTERING DATABASE OBJECTS WITH POWERSHELL. With the code that Rhys Campbell posted, I modified it a bit for solving my issue at-hand.   His PoSh code searches for database objects that contain specific text–BINGO!   I encourage anyone viewing this post to visit Rhys site and checkout the code he’s posted; from that you can use your imagination to extend the code to suit your needs when you need to quickly alter a boat-load of views, procs, triggers, etc.

So, here’s what I did… demo’ing it with good ‘ole AdventureWorks.  Since my task was narrowed down to just stored procs, I needed to figure how to limit the code to change only stored procs in certain schemas, as we have a variety of proc schemas.  I needed to find out how to restrict the searching for that particular table in certain schemas… but how? where?   Well, you can see from Rhys posted code that PowerShell naturally works with SMO (SQL Server Management Objects).  You can see this too in Management Studio by right clicking on database objects to bring up the context menu and see an option for “Start PowerShell”

Next, I looked up the SMO Object Model Diagram to get more info on the stored procedure objects.  And indeed, there is a StoredProcedure Class as noted by the Microsoft.SqlServer.Management.Smo Namespace.  Checking this out, the StoredProcedure class has a Schema Property… that “Gets or sets a String value that specifies the name of the schema that contains the stored procedure”–perfect!

Now, let’s back track a bit, so we can get the full-effect of what I did… using AdventureWorks as our demo guinea pig.  Ok, my task is to alter all the stored procs on AdventureWorks2008R2 that reference the table dbo.Employee and substitute in dbo.Employee2, a copy of a Employee table (as shown in the screen shot below).

Going back to my query of procedures, these are my 2 results:

ps_alter_sp1

With a little deducing on how to limit to a specific stored proc schema, this is what I came up with:

posh_code

And here are the results as displayed in PowerShell ISE:

ps_proc_def_update_results

And in Management Studio, you can see the changes as in the 1st proc shown below:

uspgetemployeemanagers1

Note that there are 3 other stored procs that reference the Employee table in AdventureWorks2008R2, but were not altered because those 3 procs are in the HumanResources schema, not in the dbo schema.  Also, remember to test, and re-test, and note that you can use regex in PowerShell as well to help in getting at the correct pattern matching you are looking for…

Here’s the powershell script preformatted:

# Declare variables
$server = "localhost";
$database = "AdventureWorks2008R2";
$mySchema = "dbo"
$matchText = "Employee";  # Definition text to search. Be aware this accepts a regular expression
$replaceText = "Employee2"; # Text to replace $matchText
$alter = $false;     # Set to $false to test by viewing backup/change folders after execution; Set to $true if you want the script to alter database objects
$count = 0;  # foreach loop matching count
$countln = 0; # linecount

# Create backup and change folders if not exists
if(!(Test-Path -path C:\powershell\backup\procs\)) { New-Item C:\powershell\backup\procs\ -type directory }
if(!(Test-Path -path C:\powershell\change\procs\)) { New-Item C:\powershell\change\procs\ -type directory }

$backupFolder = "C:\powershell\backup\";        # Change script folders. Need a \ (back slash) on the end
$changeFolder = "C:\powershell\change\"                # One file per object, backup & change folders

# SMO is a .Net library for working with SQL Server
# http://msdn.microsoft.com/en-us/library/ms162209(v=sql.100).aspx
# Load the SQL Management Objects assembly (Pipe out-null supresses output)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

# Create our SMO objects
# Once you create a server\instance object, you can then drill down and work with the rest of the server objects
# List SMO namespaces to use | (http://msdn.microsoft.com/en-us/library/ms162233(v=sql.100).aspx)
$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)
{
# For each matching stored prcoedure
if(($proc.TextBody -match $matchText) -and ($proc.Schema -eq $mySchema))
{
$countln++
Write-Host "$countln - "  $proc.Schema $proc.Name;
# Backup of the original proc definition
$proc.Script() | Out-File ($backupFolder + "procs\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$proc.Schema + "_" + [string]$proc.name + "_backup.sql");
# New procedure definition sql
$proc.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "procs\" + ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.Name + "_" + [string]$proc.Schema + "_" + [string]$proc.name + ".sql");
# If set to true this will change the procedure definition on the server!
if($alter)
{
$proc.TextBody = $proc.TextBody -replace($matchtext, $replaceText);
$proc.Alter();
Write-Host "Altered " $proc.Name;
}
$count++
}
}

Write-Host "Finished processing $count matches in $database on $server.";

1 Comment

  1. Thank you for sharing your info. I truly appreciate your efforts and
    I will be waiting for your further post thank you once again.

Leave a Reply