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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE Master --change this to wherever you want this table to reside GO CREATE TABLE [dbo].[BlitzIndexStats]( [Priority] [tinyint] NULL, [Finding] [varchar](255) NULL, [DatabaseName] [nvarchar](128) NULL, [Details] [varchar](2000) NULL, [Definition] [varchar](4000) NULL, [SecretColumns] [varchar](512) NULL, [Usage] [varchar](255) NULL, [Size] [varchar](255) NULL, [CaptureDate] [datetime] NOT NULL DEFAULT (sysdatetime()) ) ON [PRIMARY] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
#call the sp_BlitzIndex procedure and specify the names and data types of the results; #save the result set in the $BlitzIndex variable $BlitzIndex = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "EXEC ('exec dbo.sp_BlitzIndex @DatabaseName=''--YourDBNameHere--'',@Mode=4') WITH RESULT SETS ( ( [Priority] smallint ,[Finding] varchar(255) ,[DatabaseName] nvarchar(128) ,[Details] varchar(2000) ,[Definition] varchar(4000) ,[SecretColumns] varchar(512) ,[Usage] varchar(255) ,[Size] varchar(255) ,[Info] varchar(255) ,[URL] varchar(255) ,[CreateStatement] varchar(4000) ) )" #Query the data that we want out of our $BlitzIndex result set, and set the $BlitzIndex result set to just contain that data $BlitzIndex = $BlitzIndex | SELECT Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size | Where-Object {$_.Priority -gt 0} | Sort-Object Priority,Finding #Loop through our desired results foreach ($i in $BlitzIndex) { #we have to set these variables to keep the PowerShell from erroring when encountering sub-expression statements $Priority = $i.Priority $Finding = $i.Finding $DatabaseName = $i.DatabaseName $Details = $i.Details $Definition = $i.Definition $SecretColumns = $i.SecretColumns $Usage = $i.Usage $Size = $i.Size #Insert our results into our table Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "INSERT INTO dbo.BlitzIndexStats (Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size) VALUES ($Priority,'$Finding','$DatabaseName','$Details','$Definition','$SecretColumns','$Usage','$Size')" } |
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:
- Be sure to replace “–YourDBNameHere–“ with your database name
- 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.
Hello,
Thanks for the script, I have spent all my afternoon trying to get the sp_BlitzIndex result into table…
I’ma very happy when i found your web page but this not working for me :'(
SQL Agent job work’s fine but the table is empty :S
I think retry this tomorrow when my brain cool down ^^