CODEWITHSUNDEEP
c#sql-server-2005powershell
Andy
Andy

Reputation: 425

using Microsoft.SqlServer.SMO to create an Alter Stored Procedure sql Script?

The following powershell script seems to generate a "Create" stored procedure script successfully

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("<SERVER>")
$db = $srv.Databases.Item("<Database>")
$proc = $db.StoredProcedures.Item("<StoredProcedure>")
$proc.Script() > testScript.sql

Is there anyway to create an "Alter" using a similar approach i.e. changing the scripting options? I am referencing a SQL Server 2005 environment.

I could always add a statement to replace "CREATE PROC" with "ALTER PROC" but this seems inelegant...

Upvotes: 3

Views: 2828

Answers (1)

Andy
Andy

Reputation: 425

The following Powershell script seems to work for me:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("<SERVER>") 
$db = $srv.Databases.Item("<DATABASE>") 
$proc = $db.StoredProcedures | ?{ $_.Name -eq "<STORED PROC>"} 
$retval = $proc.ScriptHeader($true) + $proc.TextBody 
$retval > testScript.sql

Note that the passing true to the following command creates an alter Header:

$proc.ScriptHeader($true)

Upvotes: 3

Related Questions