Using PowerShell To Store sp_BlitzIndex Results In A Table

I really like Brent Ozar’s sp_BlitzIndex procedure.  It offers a lot of great insight into the indexes in your databases.  If you are not currently using it, you should check it out!

One of the things that I don’t like about it, however, is that there does not seem to be an easy way to store the sp_BlitzIndex output to a table.  This feature is currently being developed, and the parameters have already been added to the procedure, but at the time of this writing that feature is not yet available in sp_BlitzIndex version 4.5, released November 15, 2016.  If you want to follow along on the progress of this feature, you can here.

So what’s a good little DBA to do?  sp_BlitzIndex produces some valuable information, and valuable information needs to be stored in a database, right?  Trying to use INSERT INTO when executing the procedure will lead to “An INSERT EXEC statement cannot be nested” error, and trying to use SELECT INTO with an OPENROWSET leads to “The metadata could not be determined because statement ‘…‘ in procedure ‘sp_BlitzIndex’ uses a temp table.” error.

PowerShell to the rescue!

After MUCH trial and error, I was able to use my limited* PowerShell skills to take the output from sp_BlitzIndex and store it in a table.  Rather than recount the gory details of how I got this to work, I offer you the below final (?) solution.  NOTE: This has only been tested to work on sp_BlitzIndex version 4.5

First we need to create a table to store our results in:

I decided to leave off things like the URL and CREATE T-SQL statements that are returned, and only store the data I was after.

Next we use the following PowerShell script to run sp_BlitzIndex and store the results in our newly created BlitzIndexStats table.

And there you have it.  You should be able to run that PowerShell script and have the data stored in your new BlitzIndexStats table.  There are two things I want to point out before running that script:

1. Be sure to replace “–YourDBNameHere–“ with your database name
2. If you created the BlitzIndexStats table in a different database, modify “-Database master” in the last line of the script

Modify to suit your needs.  I actually wound up taking it a step farther and scheduling that code to run regularly in a SQL Agent job.

Happy Coding!

*I do not claim to be proficient in PowerShell.  My skills are limited to hacks, and copying and pasting the results of my google-fu.  I have no doubt there is a more efficient way of doing this.