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

Question

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

possible duplicate of How to create ALTER scripts instead of CREATE scripts using SMO (Server Management Object) — msarchet
– msarchet, Commented Jan 4, 2012 at 16:03
Agree w/ msarchet duplciate question. I would add, third party tools such as Red Gate SQLCompare do this and have a commmand-line interface. — Chad Miller
– Chad Miller, Commented Jan 5, 2012 at 3:06

Andy · Accepted Answer · 2012-04-10 09:45:26Z

3

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)

answered Apr 10, 2012 at 9:45

Andy

4251 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

niklasolsn Over a year ago

I want to alter a function the same way. I get an error though saying "you cannot call a method on a null-valued expression" and it seem to be refering to $retval. Suggestions? Also the testScript.sql file, is this the file containg the modifications you want to make (ie. the alter script). Thanks.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related