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 sysobjects 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)

Labels: SQL Server, SQL Server - Scripts

This entry was posted on Friday, October 12, 2007 at 11:07 AM.