Finding Database Indexes: “They’re coming outta the walls! They’re coming outta the goddamn walls!”

As someone who deals with SQL Server replication “on the regs” I’m no stranger to replication snapshots. Dealing with snapshots (wanted or otherwise) means I’m probably going to need to re-apply indexes once it’s finished. I can’t actually do that unless I know what indexes exist for a given set of tables (or even views) unless I script them out ahead of time.

The thing is: just how many indexes do I actually have? Even if you’re not having to deal with stripping them off and/or putting them back, even just knowing what your index situation is can be mighty helpful. So strap in, enjoy the “Aliens” quotes, and read about my favorite script in today’s contribution to #tsql2sday which was hosted by Bert Wagner.

“Yo! Stop your grinnin’, and drop your linen! Found ’em!”

The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it:

  1. Creates a special schema to house a temporary object,
  2. Creates a special stored procedure to run the code,
  3. Calls said stored procedure,
  4. Generates a bunch of PRINT statements that serve as the output (along with new line support for readability),
  5. Cleans up the stored procedure it generated,
  6. And finally deletes the schema it created.


I can’t take all the credit (or even much credit) for this script: I found it ages ago over on SQL Server Central’s Script’s repository, and it was an already modified version of a script that was written and posted on the SQL Solace blog way back in 2010. I’ve made a few small tweaks to it (mostly formatting related) but otherwise it’s mostly unchanged. I love it though, and it’s served me well through the years. Here’s my version of it:

“Every meal’s a banquet. Every paycheck a fortune! Every formation’s a parade!”

Here’s the thing: while I rely on this script to generate my indexes for my replicas, I can use this script for almost everything related to every index in a database.  The script itself is very useful for when you need to script them out, sure… but I like to run it from time to time on my databases to get a pulse check of just how many indexes exist. After all, no indexes are (usually) bad, and too many can be just as bad. But you don’t know what you don’t know, as they say, so even if you don’t plan on actually doing anything with indexes, just having this in your hip pocket is good. From here, once you get a check of what’s there, maybe you could use an index usage script to find ones to drop, assuming you have a lot.

Oh, and what if you wanted to take a backup of just your index creation scripts? Same thing: you could schedule this to run, and I bet without too much modification, have it write the results to a table. That way, if indexes are changed or dropped, you can easily get them back to where they were.

“I like to keep this handy. For close encounters.” (Bonus PowerShell)

Of course, sometimes I need to move quick or add in some additional info in a hurry for times like that, I still rely on PowerShell. While you can’t easily get an automation “DROP INDEX” statement like the T-SQL above does, if you want to quickly script out all your indexes in a database, you can do so with just a few lines:

For this to work you’ll need to SQL Server PowerShell module from the PowerShell gallery, and replace “servername” with your server’s host name, “instance” with the name of your instance (or “default” if it’s a default/unnamed instance), and the name of your database in place of “databasename.” This will just output to the screen, but piping the results to something Out-File will work well too.


2 thoughts on “Finding Database Indexes: “They’re coming outta the walls! They’re coming outta the goddamn walls!”

  1. Pingback: T-SQL Tuesday #104 Roundup – SQL with Bert

Comments are closed.