Powershell Scripts

Scripting Stored Procedures in Current Database


$SMOserver = new-object "Microsoft.SqlServer.Management.SMO.Server" "servername"

foreach ($proc in Get-ChildItem StoredProcedures)

{

$SavePath = “h:\Scripts\” + $proc.name + “.sql”

$scriptr = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’) ($SMOserver)

$scriptr.Server = $SMOserver

$scriptr.Options.AllowSystemObjects = $False

$scriptr.Options.ScriptDrops = $true

$scriptr.Options.ToFileOnly = $true

$scriptr.Options.Filename = “h:\Scripts\” + $proc.name + “.sql”

$scriptr.Options.Filename

$proc.name

$scriptr.Script($proc)

$scriptr.Options.ScriptDrops = $False

$scriptr.Options.AppendToFile = $True

$scriptr.Script($proc)

}