Monthly Archives: July 2012

Powershell Scripts

Scripting Triggers in Current Database

Start Powershell from SSMS within the database you want to script – note that you will have to change “servername” to the actual database server e.g. “svektestserver”

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

foreach ($proc in Get-ChildItem Tables)
{
if($proc.triggers -ne $null)
{
foreach ($trig in $proc.Triggers)
{
$SavePath = "h:tempdatabases" + $trig.name + ".sql"
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$scriptr.Server = $srv
$scriptr.Options.AllowSystemObjects = $False
$scriptr.Options.ScriptDrops = $true
$scriptr.Options.ToFileOnly = $true
$scriptr.Options.Filename = "h:tempDatabases" + $trig.name + ".sql"
$scriptr.Options.Filename
$trig.name
$scriptr.Script($trig)
$scriptr.Options.ScriptDrops = $False
$scriptr.Options.AppendToFile = $True
$scriptr.Script($trig)
}
}
}

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)

}