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.

**********************
Load SNAPINS
*****************
#SQL Provider
ps:\>add-pssnapin sqlserverprovidersnapin100
#Cmdlets
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
****************************************************************

PARAM

(
$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.”+$t.name+”\indexes\”
gci -path $path1 | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n ” | out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}
ELSE
{ $tableset =get-childitem $path -ErrorAction stop | where-object {$_.name -like “$tables”}
foreach($t in $tableset)
{
$path = $path+”\dbo.”+$t.name+”\indexes\”
gci -path $path | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n “| out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}

**************************

About these ads

About Prashanth Jayaram

I’m a Database technologist having 7+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in PowerShell, SQL and tagged . Bookmark the permalink.

2 Responses to PowerSQL – Generate Only Index Script of All Tables or Specific Tables

  1. If some one wishes to be updated with hottest technologies therefore he must
    be go to see this web site and be up to date all the time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s