During some SQL development, I ran into an instance where I wanted to evaluate the existing indexes on a table to see if any of them nearly covered my query. Additionally, given a large number of existing indexes on the table, I figured I might be removing/consolidating some. I’m very visual, so opening each index individually didn’t seem to help me track which columns were used in which index. Enter sp_index_visual.
The whole project is now stored on GitHub , but I’m going to run through the meat and potatoes.
After adding the stored procedure to a database, you can run it with the table name as the sole parameter to obtain a breakdown of the indexes on the table.
- C = clustered index
- X = unclustered index
- i = included column
-- sp_index_visual -- 2/13/2017 -- drewsk.tech CREATE PROCEDURE dbo.sp_index_visual @INQTABLE NVARCHAR(100) AS BEGIN SET NOCOUNT ON; DECLARE @COLUMNS NVARCHAR(MAX) DECLARE @QSQL NVARCHAR(MAX) SET @COLUMNS = STUFF((SELECT ', [' + NAME + '] ' FROM ( select ind.name from sys.tables t left JOIN sys.indexes ind ON ind.object_id = t.object_id WHERE t.name = @INQTABLE) TUF FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @QSQL = 'select * from ( select ind.name as indexname, tcol.name as ['+@INQTABLE+' Columns], case when ic.is_included_column = 0 then case when ind.type = 1 then ''C'' else ''X'' end else ''i'' end as columnused from sys.tables t INNER JOIN sys.columns tcol on t.object_id = tcol.object_id left JOIN sys.index_columns ic ON t.object_id = ic.object_id and ic.column_id = tcol.column_id --and ic.is_included_column = 0 left JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id left JOIN sys.indexes ind ON ind.object_id = t.object_id and ind.index_id = ic.index_id WHERE t.name = N'''+@INQTABLE+''' ) indecies PIVOT ( max(columnused) for indexname IN ('+@COLUMNS+') ) as pivottable;' EXECUTE(@QSQL) END GO
There are lots of options to improve the procedure further in the future:
- drop if exists
- order indexes by clustered, # of columns, etc
- display uniqueness constraints
- display foreign keys