Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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

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



Leave a comment on the original post [, opens in a new window]

Loading comments...