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.

I Switch drives to SQLSERVER:\

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.

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

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.