Here’s a quick way you can use PowerShell to script a Stored-Procedure with a few one-liners. This is in its basic form and you can use this code to build your own functions. I will be including both an SMO and a SQLPS versions. This is how it’s done:
Here’s the SMO version: (copy/Paste code)
### – This is the SMO Version – ###
## Load the SMO Assembly V1 version (works in V2)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
## Connect to the SQL Server and get the Stored-procedures
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) “YourServerName”
$List_storedProc = $MySQL.Databases[“Master”].StoredProcedures
## List the first 10 procedures
$List_storedProc | Select -first 10 schema, name | ft -auto
## Display selected script
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script()
## Save the script to a file
($List_storedProc | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql
In the above section, you have all the necessary SMO code to create a SQL script of a Stored-Procedure. Just change the database name and you will be able to list all the SQL Stored-Procedures for that database.
Now, here’s the SQLPS version: (copy/Paste code)
### – This is the SQLPS Version – ###
## – Change directory to the Stored-procedures folder
cd SQLSERVER:\SQL\MAX-PCWIN1\DEFAULT\Databases\master\StoredProcedures
## – Directory list the first 10 Stored-Procedures using the “-force” parameter
dir -force | select -first 10
## – Stored the result in a variable
$s = dir -force
## Display selected script
($s | ? {$_.Name -eq “Sp_Configure”}).script()
## – save the scripted item to a files
($s | ? {$_.Name -eq “Sp_Configure”}).script() | Out-File z:\Temp\Scripted_StoredProc.sql
Using SQLPS.exe or loading the SQLServer provider in your profile to gain access to your SQL engine components, it gives you a slightly edge because all the needed SMO assemblies gets loaded for you. Even if you install SQL Server 2008, PowerShell itself will not load these SMO Assemblies, it has to be part of your PowerShell profile, a separate script, or in a module.
In SQLPS, or from the PS Console (or ISE prompt) with the SQLPS module loaded, you can use the change directory “CD” path provided in the sample and change the database name with the instance ( ..\Max-PCwin1\Default\..) to yours ( ..\YourServerName\Default_or_YourInstanceName\.. ). That’s it!
cd SQLSERVER:\SQL\SrvName\DEFAULT_or_InstName\Databases\master\StoredProcedures
Go ahead and try it! You will appreciate PowerShell a lot.