PowerShell – script all objects on all databases to files

[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

}

Louis Davidson 14 May 2024

T-SQL Tuesday #174: Your Favorite Job Interview Question?

Blogs

T-SQL Tuesday is something I don’t participate in nearly enough. When I saw Kevin Feasel’s invitation for this months topic, I knew I had to answer! One of my favorite things to do is technical interviews (about stuff that I know). So much so that at one time, I had considered trying to start an … Read more

Blogs

PowerShell – script all objects on all databases to files

Subscribe for more articles

Rate this article

Pop Rivett

Related articles

T-SQL Tuesday #174: Your Favorite Job Interview Question?