>SQL Server: How to Get All Indexes List With Involved Columns Name


>

Recently a friend of mine asked for a script, for documentation purpose which can help them to create all of their indexes list with column names used in each index. I thought, I must share this simple script with my blog readers.
SELECT  Tab.[name] AS TableName,
IND.[name] AS IndexName,
SUBSTRING(( SELECT  ', ' + AC.name
FROM    sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE   Ind.[object_id] = i.[object_id]
AND ind.index_id = i.index_id
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS COLS
FROM    sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
ORDER BY TableName

Advertisements

Posted on May 4, 2011, in Index, Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 16 Comments.

  1. >Yeah, uh. This doesn't work.

  2. >Syntax error removed.. Now its working fine.

  3. Made of win, this is a beautiful query to hook up to my index usage scripts, so I can immediately see which columns are involved in the unused/under-used indexes. Nice work!!

  4. This is really one of those great scripts! Thank you so much!

  5. thanks for the scriptthere is a problem with it, however – it is putting the "included" columns into the same list as index columnsto distinguish them is_included_column flag ofsys.[index_columns] table should be used

  6. thanks, aedna…. Its corrected 😉

  7. Thanks for posting this great script! It is a big help and nice work adding the suggestions of the commenters.

  8. Great script!! beautifuly done… thanks a lot.

  9. Great, that's what I was looking for, Thank you a lot!

  10. Great. It is very helpful. Thanks a lot.

  11. Great script – would be even better if you can make it display (select) the Schema as well, before TableName

  12. Thanks to point out. Script updated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: