Sometimes you need to script out all the indexes in a database. Maybe you’re concerned something has changed since they were last checked in. Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

Enjoy! If you’ve got ideas about ways to improve this, I’d love to hear them in the comments.

TSQL For Scripting Out All Indexes in a Database

This will script out:

• Clustered and nonclustered indexes (including filtered indexes)
• The filegroup the index is created on (if not partitioned)
• The partition scheme the index is created on (if partitioned)
• Compression settings if the index is compressed– and it’s per partition if the table is partitioned
• For tables that don’t have a clustered index, they will still get a row in the results with a comment indicating it is a heap

The code is available in a gist.