View index details by Table

Here’s a handy script to see the indexes available on a given table.
The script displays all columns associated with the index, and identifies if it is a index key column, or an include.

DECLARE @TableName NVARCHAR(50)
SELECT @TableName = ''	-- Enter table name here.

SELECT 
    t.name AS TableName,
    ind.name AS IndexName,
    ind.index_id AS IndexID,
    ic.index_column_id AS ColumnID,
    col.name AS ColumnName,
	CASE
		WHEN is_included_column = 0 THEN 'Key'
		WHEN is_included_column = 1 THEN 'Included'
		ELSE ''
	END AS ColumnType,
    ind.type_desc AS IndexType
FROM 
    sys.indexes ind 
    INNER JOIN sys.index_columns ic 
            ON  ind.object_id = ic.object_id 
            and ind.index_id = ic.index_id 
    INNER JOIN sys.columns col 
            ON ic.object_id = col.object_id 
            and ic.column_id = col.column_id 
    INNER JOIN sys.tables t 
            ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND t.name = @TableName
ORDER BY 
     t.name, 
    ind.name, 
    ind.index_id, 
    ic.index_column_id

Hopefully you will find it helpful with database tuning, as I have.

Subscribe

0 comments