THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Query for index column information

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:

index information results

That’s all. Hope this is useful.

@Jamiet

Published Thursday, September 16, 2010 11:25 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alejandro Mesa said:

Jamie,

No doubt that this is a useful query. You could sort the columns by  the "key_ordinal" column from sys.index_columns, so you can get the list by the key position.

Check this stored procedure from Tibor Karaszi, it gives you more index information, like included columns, etc.

sp_indexinfo

http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Thanks for sharing this with us.

--

AMB

September 18, 2010 2:36 PM
 

nilofer said:

i do not like this post................

September 20, 2010 1:33 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement