Printed 2014/09/02 08:48PM

PowerSQL – Generate Only Index Script of All Tables or Specific Tables

By Prashanth Jayaram, 2013/04/24

Background of this implementation-As a part of Performance tuning, We noticed huge avg_disk_queue_length on F drive after analysis, we’ve recommended to move two tables(data and index)to different drive all together a different filegroup.  This would give the best IO performance. Those two tables have ‘N’ indexes. Manually scripting ‘N’ index is going to be daunting task.

The below are the sequence of steps to create index script.

Manually load the SQL Server snap-ins into PowerShell, run it on the computer where SQL Server is installed.

#SQL Provider
ps:\>add-pssnapin sqlserverprovidersnapin100
ps:\>add-pssnapin sqlservercmdletsnapin100

Five Parameter Details -
$Server = Local or remote server ( Localhost or Server01 or Server 02)
$Instance = Default/NamedInstanceName ( default or TMG or KAT)
$Database = DatabaseName ( dssp or webp)
$Schema = Schema Name( dbo or any schema name)
$Tables = Enter ‘*’ [All tables] or Name of the specific Table(Work_order)

Output:- F:\PowerSQL\index.txt ( Change the path)

Actual PowerShell Script


$server = $(read-host “Server”),
$instance = $(read-host “Instance – Default”),
$database = $(read-host “Database”),
$schema = $(read-host “schema (default schema dbo)”),
$tables = $(read-host “Tables (*)”)

$path = “sqlserver:\sql\$server\$instance\databases\$database\tables”

IF ($tables -eq ‘*’)
$tableset= gci -path $path | select-object name
foreach($t in $tableset)
$path1 = $path+”\dbo.”+$”\indexes\”
gci -path $path1 | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n ” | out-file f:\PowerSQL\index.txt -append; $_.Name;}
{ $tableset =get-childitem $path -ErrorAction stop | where-object {$ -like “$tables”}
foreach($t in $tableset)
$path = $path+”\dbo.”+$”\indexes\”
gci -path $path | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n “| out-file f:\PowerSQL\index.txt -append; $_.Name;}


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.