Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

On the Fringe

Chim Kalunta is a Database Systems Developer and Independent Consultant working out of the UK. An MCITP: Database Developer and an MCITP: Database Administrator, he can be reached at chimkalunta.com.

Index Scripting with Powershell

I’ve been actively seeking out opportunities to do more stuff with Powershell of late.

I had wanted to script out only the Indexes on Tables in a Database and remembered SQL Server┬áManagement Studio doesn’t make this as easy as it could be.

I can’t say that I’ve done my due diligence here to find out if SQL Server Data Tools (SSDT) makes this process any easier┬ábut I thought I’d have a go at doing it with Powershell just the same.

At the Powershell Command prompt, I add the SQL Server Snap-Ins.

AddSnapIns

I Switch drives to SQLSERVER:\

SetLocationSqlServer

I set my location to the SQL folder, also specifying the SQL Server Instance and Database I’d like to connect to, while navigating the SQL Server Provider Path hierarchy until I hit the Tables Collection.

 

SetLocationTables

I then just type in the following at the prompt to get what I want:

ScriptIndexes

 

The Get-ChildItem command retrieves all the Objects at our current location, in this case, the Tables, and they are piped or passed to the ForEach statement.

The ForEach construct loops over all the available Tables and in turn, their Indexes.

The Script() Method is called on each Index Object to get the TSQL I need.

IndexesScript

 

Comments

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

Loading comments...