More on indices (SQL Server 2k8)

The following snippets are an easy way to have a peek at the indices of a table (or their details). Both work with Action set to Display Output Results.

Listing indices of the selected table:

Code:

-- index list for a table
$PROMPT(show_disabled,List disabled indices?,,,Yes No)$
$$
SELECT
s.[schema_id]
,s.[name] AS [schema_name]
,t.[object_id] AS table_id
,t.[name] AS table_name
,i.index_id
,i.[name] AS index_name
,i.type_desc
,i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter
,COUNT(ic.index_column_id) - SUM(CAST(ic.is_included_column as SMALLINT)) AS ic_count
,SUM(CAST(ic.is_included_column as SMALLINT)) AS iic_count
FROM
sys.schemas s
INNER JOIN
sys.tables t
   ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i
   ON i.object_id = t.object_id
INNER JOIN
sys.index_columns ic
   ON ic.object_id = t.object_id
   AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
   ON c.object_id = t.object_id
   AND ic.column_id = c.column_id
WHERE i.index_id > 0
AND i.type IN ( 1 ,2 ) -- clustered & nonclustered only
AND i.is_hypothetical = 0
AND ( i.is_disabled = 0 OR '$show_disabled$' = 'Yes')
AND t.[object_id] = OBJECT_ID('$OBJECT$')
GROUP BY
s.[schema_id], s.[name], t.[object_id], t.[name], i.index_id, i.[name],i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter,i.type_desc
ORDER BY
i.[name]
$$

Show the details of an index of the selected table (identified by index_id you can get by using the snippet above):

Code:

-- detailed index view for a table
$PROMPT(idix,Index id?)$
$$
SELECT
s.[schema_id]
,s.[name] AS [schema_name]
,t.[object_id] AS table_id
,t.[name] AS table_name
,i.index_id
,i.[name] AS index_name
,i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter
,ic.index_column_id, c.[name] AS column_name
,CASE WHEN ic.is_descending_key = 0 THEN 'ASC'
   ELSE 'DESC'
END AS [order]
,ic.is_included_column
FROM
sys.schemas s
INNER JOIN
sys.tables t
   ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i
   ON i.object_id = t.object_id
INNER JOIN
sys.index_columns ic
   ON ic.object_id = t.object_id
   AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
   ON c.object_id = t.object_id
   AND ic.column_id = c.column_id
WHERE i.index_id > 0
AND i.type IN ( 1 ,2 ) -- clustered & nonclustered only
AND i.is_hypothetical = 0
AND t.[object_id] = OBJECT_ID('$OBJECT$')
AND i.index_id = $idix$
ORDER BY
i.[name]
,ic.index_column_id
$$