Script Those Indexes

Kendra Little provides a T-SQL script to script out all indexes on a database:

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.

The fact that the built-in Generate Scripts does not include compression is annoying, but Kendra’s script does.  For bonus points, use Powershell to update scripts automatically with index changes and check them into your source control system of choice.

Related Posts

Query Store Indexes

Arthur Daniels shows what you can learn from the indexes on Query Store tables: It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. […]

Read More

Gotchas When Indexing Partitioned Tables

Andrew Pruski gives us a couple of considerations when creating indexes on partitioned tables in SQL Server: Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not. But also […]

Read More


May 2016
« Apr Jun »