List all the Columns of an Index in Current Database in SQL Server

Last week I was writing a few validation scripts for some of the recent schema changes / performance tuning which we had done in our project. As part of this we had defined few indexes and most of them were on multiple columns. As a result of this, during validation process, I wanted to list all the columns of all the indexes in a particular database so that by looking at the output I can make sure that all the indexes are created as expected with the required columns in it. After doing some research  on some of the system tables (like sys.objects, sys.indexes etc.), I came up with a simple SQL script which will list all the columns contained in all the indexes in the current database.

DB_NAME() AS DatabaseName
, AS SchemaName
, AS ObjectName
, DB_NAME() + ‘.’ + + ‘.’ + AS FullyQualifiedObjectName
, SObj.type AS ObjectType
WHEN SObj.type = N’U’ THEN ‘Table’
WHEN SObj.type = N’V’ THEN ‘View’
END AS ObjectTypeDesc
, AS IndexName
, SIdx.type AS IndexType
, SIdx.type_desc AS IndexTypeDesc
, SIdx.is_primary_key AS IsIndexAPrimaryKey
, SIdxCol.index_column_id AS IndexColumnSequence
, AS IndexColumnName
sys.objects SObj
INNER JOIN sys.schemas SSch
ON SObj.schema_id = SSch.schema_id
INNER JOIN sys.indexes SIdx
ON SObj.object_id = SIdx.object_id
INNER JOIN sys.index_columns SIdxCol
ON SIdx.object_id = SIdxCol.object_id
AND SIdx.index_id = SIdxCol.index_id
INNER JOIN sys.columns SCol
ON SIdxCol.object_id = SCol.object_id
AND SIdxCol.column_id = SCol.column_id
WHERE SObj.type IN (N’U’, N’V’)
ORDER BY DatabaseName, SchemaName, ObjectName, IndexName, IndexColumnSequence

Running this query in AdventureWorks database will produce the following output:


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on October 15, 2010, in Code Snippets, SQL Server, T-SQL, T-SQL Snippets. Bookmark the permalink. 1 Comment.

What are your thoughts?

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

You are commenting using your 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