Sql-server – Insert results of spBlitzIndex stored procedure into table

sp-blitzindexsql serversql server 2014stored-procedures

I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results.

Here's what I've tried:

INSERT INTO dbo.[IndexBlitzResults]
           ([PK_IndexBlitzID]
           ,[Priority]
           ,[Finding]
           ,[DatabaseName]
           ,[Details]
           ,[Definition]
           ,[SecretColumns]
           ,[Usage]
           ,[Size]
           ,[MoreInfo]
           ,[URL]
           ,[CreateTSQL])
exec    [ozar].[sp_BlitzIndex]
        @Mode = 0,
        @GetAllDatabases = 1,
        @BringThePain = 1

And here's the error that it throws:

Starting run. sp_BlitzIndex(TM) v5.4 – June 03, 2017 Create temp
tables. Adding UQ index on #IndexSanity (database_id, object_id,
index_id)

redacted_db Inserting data into #IndexColumns for clustered indexes
and heaps Failure populating temp tables. Last @dsql: SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;
SELECT 29,
s.name,
si.object_id,
si.index_id,
sc.key_ordinal,
sc.is_included_column,
sc.is_descending_key,
sc.partition_ordinal,
c.name as column_name,
st.name as system_type_name,
c.max_length,
c.[precision],
c.[scale],
c.collation_name,
c.is_nullable,
c.is_identity,
c.is_computed,
c.is_replicated,
c.is_sparse,
c.is_filestream,
CAST(ic.seed_value AS BIGINT),
CAST(ic.increment_value AS INT),
CAST(ic.last_value AS BIGINT),
ic.is_not_for_replication
FROM [redacted_db].sys.indexes si
JOIN [redacted_db].sys.columns c ON
si.object_id=c.object_id
LEFT JOIN [redacted_db].sys.index_columns sc ON
sc.object_id = si.object_id
and sc.index_id=si.index_id
AND sc.column_id=c.column_id
LEFT JOIN [redacted_db].sys.identity_columns ic ON
c.object_id=ic.object_id and
c.column_id=ic.column_id
JOIN [redacted_db].sys.types st ON
c.system_type_id=st.system_type_id
AND c.user_type_id=st.user_type_id
JOIN [redacted_db].sys.objects AS so ON si.object_id = so.object_id
AND so.is_ms_shipped = 0
JOIN [redacted_db].sys.schemas AS s ON s.schema_id = so.schema_id
WHERE si.index_id in (0,1) OPTION (RECOMPILE);

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1427 [Batch
Start Line 2] redacted_db database failed to process. An INSERT EXEC
statement cannot be nested. Msg 3915, Level 16, State 0, Procedure
sp_BlitzIndex, Line 1431 [Batch Start Line 2] Cannot use the ROLLBACK
statement within an INSERT-EXEC statement.

I looked into using openrowset, but from what I could tell you can't pass parameters to the stored procedure.

So, how can I insert the results of this stored procedure into the table?

Best Answer

I think there is a open request to have this fixed.

It does appear that they have made some changes in March 2017 as referenced here:

  • Added Output parameters to sp_BlitzIndex to send Mode 2 results to permanent repository

I also found a workaround that uses Powershell.

Using PowerShell To Store sp_BlitzIndex Results In A Table.

I'm copying the important information from that post.

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

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.

#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')"
}

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
Related Question