0

I have latest version of sp_blitzindex. Would like to store results in table. Runs no output, anyone doing this already?

Running with parameters below:

EXEC dbo.sp_BlitzIndex @databasename = 'ngprod',
    @OutputDatabaseName = 'dbaadmin',
    @OutputSchemaName = 'dbo',
    @OutputTableName ='BlitzIndexStats'

Table structure is below:

USE [DBAAdmin]
GO

/****** Object:  Table [dbo].[BlitzIndexStats]    Script Date: 6/7/2017 9:08:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
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
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlitzIndexStats] ADD  DEFAULT (sysdatetime()) FOR [CaptureDate]
GO
  • I would update sp_BlitzIndex to insert the data into your custom table. You should be able to add an INSERT INTO right after the /*Return results.*/ comment using the same select block immediately after the comment-- which returns the results. – Jacob H Jun 7 '17 at 14:30
1

I downloaded the latest sp_BlitzIndex and in the comments in the SP i found this:

So you are not able to use that parameter.

  • Ok dumb question does the output to a table work (store results in database that the sp is being run against? – tcstl Jun 7 '17 at 14:58
  • That should work. I haven't tried it but looking at the code it looks like it should work. – Daniel Björk Jun 7 '17 at 15:03
1

I was able to get output saved to a table using sp_BlitzCache v5.6 but only with @mode=2.

Inside the proc I can see that its @Version = '5.6' and @VersionDate = '20170801' which as of this writing was yesterday. I had a version that was probably a month old or so and @Mode=2 was not working so this appears to be a recent fix.

My command:

  exec sp_blitzindex @mode=2, @OutputDatabaseName=N'OUTPUT_DB', @OutputSchemaName=N'dbo', @OutputTableName=N'BLITZINDEX', @DatabaseName= N'TARGET_DB';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.