Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
# Adapted from here
# Also see this one:
<# 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
It will create the subfolders for the databases and objects if necessary.
#-- These settings are unique to your database and system. Update as needed...
$path = "H:\Work\LM5\LMDB_Scripts\" # Location of output files. Will create folders if they are not present.
$ServerName = "\SqlExpress"
$IncludeDatabases = @("LMDB", "BLAH") #<-- Add your database(s) here...
$databaseUserName = "MyUsername"
$databasePassword = "MyPassword"
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName
#-- Authenication to server
$serverInstance.ConnectionContext.LoginSecure = $false # False means do not use Windows auth, use Sql Server auth instead
$IncludeTypes = @("tables", "StoredProcedures", "Views", "UserDefinedFunctions", "Schemas")
$ExcludeSchemas = @("sys", "Information_Schema")
# -- Scripting output options
$so = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
$so.IncludeIfNotExists = 0
$so.SchemaQualify = 1
$so.AllowSystemObjects = 0
$so.ScriptDrops = 0 # Script Drop Objects ?
$so.Indexes = $true # Include code for Indexes?
$so.NonClusteredIndexes = $true # Include code for Non Clustered Indexes?
$dbs = $serverInstance.Databases
foreach ($db in $dbs)
$dbname = "$db".replace("[", "").replace("]", "")
$dbpath = "$path"+"$dbname" + "\"
if ($dbname -NotIn $IncludeDatabases)
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 -Encoding "UTF8" -Force
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.