Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# Adapted from here https://www.simple-talk.com/blogs/powershell-script-all-objects-on-all-databases-to-files/
# Also see this one: https://gist.github.com/cheynewallace/9558179
<# 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 = "192.168.0.7\SqlExpress"
$IncludeDatabases = @("LMDB", "BLAH") #<-- Add your database(s) here...
$databaseUserName = "MyUsername"
$databasePassword = "MyPassword"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$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
$serverInstance.ConnectionContext.set_Login($databaseUserName)
$serverInstance.ConnectionContext.set_Password($databasePassword)
$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)
{
continue
}
$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"
$OutFile
$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.