On nearly every project I ever work on I end up crafting a query that tells me all the information about the indexes in a database. I do of course keep a collection of useful scripts hanging around though when I’m on client site its not always accessible and given how often I end up writing this damn thing I figured I’d just make it easy on myself I stick it on my blog so its easy to find next time and hey, maybe someone else can make use of this too.
Here’s the query in question. It pulls out pertinent information about each index in the database including the columns which it displays in a comma-separated list:
;WITH CTE AS (
SELECT ic.[index_id] + ic.[object_id] AS [IndexId],t.[name] AS [TableName]
,i.[name] AS [IndexName],c.[name] AS [ColumnName],i.[type_desc]
,i.[is_primary_key],i.[is_unique]
FROM [sys].[indexes] i
INNER JOIN [sys].[index_columns] ic
ON i.[index_id] = ic.[index_id]
AND i.[object_id] = ic.[object_id]
INNER JOIN [sys].[columns] c
ON ic.[column_id] = c.[column_id]
AND i.[object_id] = c.[object_id]
INNER JOIN [sys].[tables] t
ON i.[object_id] = t.[object_id]
)
SELECT c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
,STUFF( ( SELECT ','+ a.[ColumnName] FROM CTE a WHERE c.[IndexId] = a.[IndexId] FOR XML PATH('')),1 ,1, '') AS [Columns]
FROM CTE c
GROUP BY c.[IndexId],c.[TableName],c.[IndexName],c.[type_desc],c.[is_primary_key],c.[is_unique]
ORDER BY c.[TableName] ASC,c.[is_primary_key] DESC;
And here’s the output it produces when run against [AdventureWorksDW], note the list of columns:
That’s all. Hope this is useful.
@Jamiet