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