sp_betterhelpindex: A better sp_helpindex

Microsoft’s system sprocs are nice and all, but they’re rather basic. I have written an improvement to sp_helpindex which also includes key columns, included columns, reads and writes, fragmentation, and freshness of statistics. Like sp_helpindex, it goes in master but can be called from any database. Download the script with proper tabbing.

USE master
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'sp_betterhelpindex')
   DROP PROCEDURE sp_betterhelpindex
GO

CREATE PROC dbo.sp_betterhelpindex @tableName VARCHAR(255) AS

DECLARE @sql VARCHAR(MAX)

SET @sql ='
SET NOCOUNT ON

USE ' + DB_NAME() + '

DECLARE @object_id INT, @index_id INT, @indexName VARCHAR(500)
DECLARE @keyColumns VARCHAR(2000), @includedColumns VARCHAR(2000)

SET @object_id = OBJECT_ID(''' + @tableName + ''')

DECLARE @index TABLE (
   index_id INT,
   keyColumns VARCHAR(2000),
   includedColumns VARCHAR(2000)
)

DECLARE sp_betterhelpindex_indexes CURSOR FAST_FORWARD FOR
   SELECT index_id
   FROM sys.indexes
   WHERE object_id = @object_id

OPEN sp_betterhelpindex_indexes
FETCH sp_betterhelpindex_indexes INTO @index_id
WHILE @@fetch_status = 0
BEGIN
   SELECT @keyColumns = NULL, @includedColumns = NULL

   SELECT @keyColumns = ISNULL(@keyColumns + '', '', '''') +
   columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
   FROM caV2.sys.indexes indexes
   INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
   AND indexes.index_id = index_columns.index_id
   INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
   AND index_columns.column_id = columns.column_id
   WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 0
   ORDER BY index_column_id

   SELECT @includedColumns = ISNULL(@includedColumns + '', '', '''') +
   columns.name + (CASE WHEN is_descending_key = 1 THEN ''(-)'' ELSE '''' END)
   FROM caV2.sys.indexes indexes
   INNER JOIN caV2.sys.index_columns index_columns ON indexes.object_id = index_columns.object_id
   AND indexes.index_id = index_columns.index_id
   INNER JOIN caV2.sys.columns columns ON index_columns.object_id = columns.object_id
   AND index_columns.column_id = columns.column_id
   WHERE indexes.object_id = @object_id AND indexes.index_id = @index_id AND is_included_column = 1
   ORDER BY index_column_id

   INSERT INTO @index VALUES (@index_id, @keyColumns, ISNULL(@includedColumns, ''''))

   FETCH sp_betterhelpindex_indexes INTO @index_id
END
CLOSE sp_betterhelpindex_indexes
DEALLOCATE sp_betterhelpindex_indexes

SELECT sys.indexes.name,
   (CASE WHEN is_primary_key = 1 THEN ''primary key, ''
   WHEN is_unique = 1 THEN ''unique, ''
   ELSE '''' END) + LOWER(sys.indexes.type_desc) AS type,
   keyColumns, includedColumns,
   sys.filegroups.name AS [filegroup],
   user_seeks + user_scans + user_lookups AS readsByQueries, user_updates AS updates,
   CAST(physicalStats.avg_fragmentation_in_percent AS INT) AS [fragmentation %],
   STATS_DATE (@object_id , i.index_id) AS statisticsUpdated, i.index_id
FROM @index i
INNER JOIN sys.indexes ON sys.indexes.object_id = @object_id AND i.index_id = sys.indexes.index_id
INNER JOIN sys.filegroups ON sys.indexes.data_space_id = sys.filegroups.data_space_id
INNER JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = @object_id
   AND i.index_id = usage.index_id
   AND usage.database_id = DB_ID()
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), @object_id, NULL, NULL, NULL) physicalStats
   ON i.index_id = physicalStats.index_id AND alloc_unit_type_desc != ''LOB_DATA''
ORDER BY sys.indexes.type_desc, keyColumns, includedColumns
'

EXEC(@sql)

Attached File sp_betterhelpindex.sql_.txt

Leave a Reply

Your email address will not be published. Required fields are marked *


*