Archive

Archive for September, 2011

Script Indexes on all Tables using PowerShell.

September 19, 2011 1 comment

This is going to be quick post.. This morning I got requested to script only indexes on all tables in DB.

First I tried to search around in SSMS to see if there is any option to script only indexes on all tables but no luck. Finally after an hour of work with my little knowledge of PowerShell I was able to generate all the index script.. This is going to be part of my scriptbox for future usage.

In SSMS browse to tables and right click. Select start powershell . This will open powershell console window with path set to tables. Run below script to script indexes only.

Script :

$tbl = gci | select-object name
foreach($t in $tbl)
{
$path = “”
$path = “dbo.”+$t.name+”\indexes\”
gci -Path $path | %{$_.Script() | out-file c:\index.txt -append; “GO `r`n ” | out-file c:\index.txt -append; $_.Name;}
}

There might be several other better ways in powershell to do it but for now I could get this work using above script.. I would try to modify this script by adding options to Drop\Create indexes rather than create only in future version if get chance 🙂

NJ

Advertisements
Categories: Uncategorized