How to Script SQL Server Objects in Powershell?

<#################################### How to script tables, USPs etc ###########################################>
#Scripting all tables
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2014\tables> gci | %{$_.script()}

# How to script tables with options
# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the script option object
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so=new-object microsoft.sqlserver.management.smo.scriptingoptions

# Step 3 - Check the properties and methods avialable to the script option object
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so | gm

# Step 4 - Now we can set some properties such as IncludeDatabaseContext or NoFileGroup and pass that to the script() method.
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.IncludeDatabaseContext=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.NoFileGroup=1

# The script below works fine in SQL Server 2008/R2, however, the script{$so) causes an error in SQL Server 2012 as below:
# script : Cannot find an overload for "Script" and the argument count: "1".
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> gci | % {$_.Script($so)}

# If we want the results from multiple cmdlets concatenated, we use the append parameter.
PS SQLSERVER:\sql\xu_xps\R2\databases\adventureworks2008r2\tables> gci | % {$_.Script($so)} | Out-File c:\ScriptingTables.txt -append



###################### Script PK/FKs in Powershell #####################

#Step 1 - go to the right location to see the available propeties

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a=dir
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | gm

#Step 2 - script all FKs to the screen
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.ForeignKeys}|%{$_.Script()}

#Step 3 - script all indexes to the screen
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script()}

#Step 4 - script all indexes to a file
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script() | Out-file "C:\indexes.sql" -append}

#Step 5 - script all indexes to a file, adding "GO" to make each index creation as a separate batch, instead of treating all of the index creation as a single batch.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script() | Out-file "C:\indexes.sql" -append; "GO" |Out-file c:\indexes.sql -append; $_.name}

#Step 6 - Change and use the scripting options
# Since we use SQLPS from SQL Server, we can create a scripting object without loading the assembly
# The cmdletsbelow create a new scripting option object
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $SObj=new-object Microsoft.sqlserver.management.smo.scriptingoptions

#List all of the properties for scripting options:

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $Sobj

<#
FileName                              :
Encoding                              : System.Text.UnicodeEncoding
DriWithNoCheck                        : False
IncludeFullTextCatalogRootPath        : False
BatchSize                             : 1
ScriptDrops                           : False
TargetServerVersion                   : Version120
TargetDatabaseEngineType              : Standalone
AnsiFile                              : False
AppendToFile                          : False
ToFileOnly                            : False
SchemaQualify                         : True
IncludeHeaders                        : False
IncludeIfNotExists                    : False
WithDependencies                      : False
DriPrimaryKey                         : False
#>

# Now we can change these scripting options such as Scriptdrops, and use them. The cmdlets below first change the scriptdrop to true,then call the value to create the drops of the indexes.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $Sobj.ScriptDrops=1
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script($SObj) | Out-file "C:\indexesdrop.sql" -append; "GO" |Out-file c:\indexesdrop.sql -append; $_.name}


<################# How to script all of the indexes for all tables in a database ###########>
# Option 1 - Scripting all indexes to the console from PS SQLSERVER:\SQL\xu_xps\DEFAULT\DATABASES\ADVENTUREWORKS2012\TABLES
PS SQLSERVER:\SQL\xu_xps\DEFAULT\DATABASES\ADVENTUREWORKS2012\TABLES> GCI | % {ForEach ($ix in $_.Indexes){Write-Host $ix.Script()}}

# Option 2 - Scripting all indexes to the console from Windows PowerShell (not ISE!)

# Step 1 - Assume you have the ScriptIndexes.ps1 as below in c:\users\charlie\documents
#GET-LOCATION
CD SQLSERVER:\  #--very critical to have "\" here, otherwise, it will get the wrong path!
#Get-Location
CD SQL\LOCALHOST\DEFAULT\DATABASES\REPORTSERVER\TABLES
#Get-Location
DIR | % {`
            CD \;
            $PATH=$_.URN.ToString() | convert-urnToPath;
            cd $Path\Indexes;
            GCI | % {$_.Script() }
            }
#Get-Location
cd c:
#Get-Location

Step 2: - Execute the code as below:
PS C:\users\charlie\documents> . ./ScriptIndexes.ps1 # The . ./ is for dot sourcing.


# It is easy to check the compatibility level for all of the databases in a SQL Server instance as below. But it's hard to change them.
# You can change it one by one, or use a cursor to loop through.
# --T-SQL query: select name, compatibility_level from sys.databases

# Alternatively, 2 ways in PowerShell to chanage them:

PS SQLSERVER:\sql\localhost\default\databases> gci | % {$_.Set_compatibilityLevel(100);$_.Alter()}

PS SQLSERVER:\sql\localhost\default\databases> gci | % {$_.CompatibilityLevel=120;$_.Alter()}


PS SQLSERVER:\sql\localhost\default\databases> gci | ft Name, CompatibilityLevel -autosize