Extract scripts from SQL Server.ps1

# 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
                      }
              }
       }     
}