Powershell – script all objects on all databases to files
- by Nigel Rivett
<#
This simple PowerShell routine scripts out all the user-defined functions,
stored procedures, tables and views in all the databases on the server that
you specify, to the path that you specify.
SMO must be installed on the machine (it happens if SSMS is installed)
To run - set the servername and path
Open a command window and run powershell
Copy the below into the window and press enter - it should run
It will create the subfolders for the databases and objects if necessary.
#>
$path = “C:\Test\Script\"
$ServerName = "MyServerNameOrIpAddress"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @(“tables”,”StoredProcedures”,"Views","UserDefinedFunctions")
$ExcludeSchemas = @(“sys”,”Information_Schema”)
$so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)
$so.IncludeIfNotExists = 0
$so.SchemaQualify = 1
$so.AllowSystemObjects = 0
$so.ScriptDrops = 0 #Script Drop Objects
$dbs=$serverInstance.Databases
foreach ($db in $dbs)
{
$dbname = "$db".replace("[","").replace("]","")
$dbpath = "$path"+"$dbname" + "\"
if ( !(Test-Path $dbpath))
{$null=new-item -type directory -name "$dbname"-path "$path"}
foreach ($Type in $IncludeTypes)
{
$objpath = "$dbpath" + "$Type" + "\"
if ( !(Test-Path $objpath))
{$null=new-item -type directory -name "$Type"-path "$dbpath"}
foreach ($objs in $db.$Type)
{
If ($ExcludeSchemas -notcontains $objs.Schema )
{
$ObjName = "$objs".replace("[","").replace("]","")
$OutFile = "$objpath" + "$ObjName" + ".sql"
$objs.Script($so)+"GO" | out-File $OutFile #-Append
}
}
}
}
