Wayne Sheffield

My blog about SQL Server

Archives

Categories

Welcome to Day 27 of my "A Month of PowerShell" series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

One of the tasks that we are frequently called upon to perform is to script out objects from one server and to apply them to another. Fortunately, PowerShell makes this easy.

Virtually every object in the SMO library has a Script method. Actually, they have two, one without parameters and one that accepts a ScriptOptions collection for how to control the scripting. Want to script out an entire database? Navigate to the database, and call the Script() method. Need a procedure? Navigate to the procedure, and call the Script() method. Views? Functions? Tables? Indexes? They all have a Script() method that you can use.

What’s really neat is that with every version of SQL Server, the SMO libraries are updated to handle the new features. For instance, SQL Server 2012 introduced a ColumnStore index. The Script() method for the index can script it out.

Have you ever written a T-SQL script to script out all of the indexes in a database? It can get quite long (the incomplete version that I have is over 300 lines of code)... and are you certain that it handles everything correctly? Here’s my PowerShell script that will script out any index. Over half of this script is parameters, comments and code to verify parameters and to get it ready to send the output to a file. The actual scripting of all of the indexes is less than 40 lines... let’s see you do that in T-SQL. As an added bonus, the next version of SQL Server will probably include clustered ColumnStore indexes... this script will generate those without any modifications, since the logic to generate the script is in the SMO library, not in PowerShell (the only thing that I might have to change is the method of ensuring that the SMO libraries are loaded... that keeps changing from version to version). Here is my PowerShell script:

PowerShell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#
# Syntax: & GetIndexes.ps1 "SQLServer\Instance" "Database" "OutputPathAndFileName"
#
# In order for PowerShell to run scripts, run Set-ExecutionPolicy RemoteSigned
#
# Notes:
# 1. If the specified output file already exists, it is deleted without warning.
# 2. If the specified output path does not exist, it is created.
#---------------------------------------------------------------------------------------------------
# PARAMETERS (Required)
#---------------------------------------------------------------------------------------------------
# $SQLInstance [string] - the name of the sql instance, in server\instance format.
# $Database [string] - the name of the database to script out indexes for.
# $FileName [string] - the location of the output file.
#---------------------------------------------------------------------------------------------------
# PARAMETERS (Optional)
#---------------------------------------------------------------------------------------------------
# $Schema [string] - the name of the schema to script out indexes for. If not specified, uses all.
# $Table [string] - the name of the table to script out indexes for. If not specified, uses all.
# $IncludeDrop [boolean] - set to true (default) to have the script generate drop statements and
# existance check prior to dropping.
# $AppendToFile [boolean] - set to true (default) to append to the file. False will overwrite the file.
# $IncludeHeaders [boolean] - set to true (default) to include scripting comments.
# $NoFileGroup [boolean] - set to true (default) to suppress filegroup placement options in generated script.
# $ToFileOnly [boolean] - set to true (default) to send generated script to the file only.
# Setting to false will also send the generated script to the screen.
#---------------------------------------------------------------------------------------------------
# MODIFICATION LOG
#---------------------------------------------------------------------------------------------------
#2012年08月31日 WGS Initial Creation.
#---------------------------------------------------------------------------------------------------
param(
[String]$SQLInstance,# the SQL instance to generate a script from, in Server\Instance format.
[String]$Database,# the database to generate the index scripts from.
[String]$Filename,# the output file for the generated scripts.
#Optional parameters follow
[String]$SchemaName,# if specified, restricts generated scripts to tables in this schema.
[String]$TableName,# if specified, restricts generated scripts to tables with this name.
[boolean]$IncludeDrop=$True,# set to $True to include drop statements
[boolean]$AppendToFile=$True,# set to $False to overwrite file
[boolean]$IncludeHeaders=$True,# set to $False to not have the scripting headers
[boolean]$NoFileGroup=$True,# set to $False to include filegroup placement in script
[boolean]$ToFileOnly=$True# set to $False to send results to screen also
)
cls
if((!$SQLInstance)-or(!$Database)-or(!$Filename))
{
Write-Warning'Syntax: & "GetIndexes.ps1" "SQLServer\Instance" "DatabaseName" "Output Path and Filename" '
Write-Warning'Syntax (Optional Parameters): '
Write-Warning' "SchemaName"'
Write-Warning' "TableName"'
Write-Warning' "Include Drop Statements" (boolean)'
Write-Warning' "Append each object''s script to file" (boolean)'
Write-Warning' "Include Script Headers" (boolean)'
Write-Warning' "Don''t include filegroup placement settings" (boolean)'
Write-Warning' "Send script output to file only" (boolean)'
Write-Warning' "Pass in boolean values as $True or $False"'
Write-Host""
}
else
{
# if the file already exists, delete it
if(Test-Path$Filename){Remove-Item$Filename}
# get the path that this file is in
$Path=Split-Path$Filename-Parent
# if the path doesn't exist, create it
if(!(Test-Path-path$Path)){New-Item$Path-TypeDirectory}
Write-Host"Output file: $Filename"
#Load SMO, connect to server and database
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|out-null
$server=New-Object('Microsoft.SqlServer.Management.SMO.Server')"$SQLInstance"
$db=$server.Databases[$Database]
# this object is used to script the actual index
$Scriptr=New-Object('Microsoft.SqlServer.Management.Smo.Scripter')($server)
$Scriptr.Options.ScriptDrops=$False
$Scriptr.Options.Indexes=$True
$Scriptr.Options.IncludeHeaders=$IncludeHeaders
$Scriptr.Options.ClusteredIndexes=$True
$Scriptr.Options.NonClusteredIndexes=$True
$Scriptr.Options.XmlIndexes=$True
$Scriptr.Options.AppendToFile=$AppendToFile
$Scriptr.Options.FileName=$Filename
$Scriptr.Options.ToFileOnly=$ToFileOnly
$Scriptr.Options.NoFileGroup=$NoFileGroup
foreach($tablein$db.Tables)
{
if(((!$TableName)-or($TableName-eq$table.name))-and
((!$SchemaName)-or($SchemaName-eq$table.Schema)))
{
foreach($indexin$table.Indexes)
{
Write-Host"Database: $db; Table: $table; Index: $index"
if($IncludeDrop-eq$True)
{
$Scriptr.Options.IncludeIfNotExists=$True
$Scriptr.Options.ScriptDrops=$True
$Scriptr.Script($index)
}
$Scriptr.Options.IncludeIfNotExists=$False
$Scriptr.Options.ScriptDrops=$False
$Scriptr.Script($index)
}
}
}
}

Like this:

Like Loading...
PowerShell none
  • #1 | Written by ShawnMTherrien about 12 years ago. Reply

    Thank you very much 🙂

    Loading...
  • #2 | Written by Bidyut about 10 years ago. Reply

    Very useful script, It worked for me . I enhanced the script to script contraints, tables, procedures, functions.

    Loading...

No trackbacks yet.

Leave a Reply to let me know how you liked this postCancel reply

[フレーム]

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress Web Design by SRS Solutions © 2025 Wayne Sheffield Design by SRS Solutions
Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage {vendor_count} vendors Read more about these purposes
View preferences
{title} {title} {title}
[フレーム]

AltStyle によって変換されたページ (->オリジナル) /