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.

SELECT
DB_NAME() AS DatabaseName
, SSch.name AS SchemaName
, SObj.name AS ObjectName
, DB_NAME() + ‘.’ + SSch.name + ‘.’ + SObj.name AS FullyQualifiedObjectName
, SObj.type AS ObjectType
, CASE
WHEN SObj.type = N’U’ THEN ‘Table’
WHEN SObj.type = N’V’ THEN ‘View’
END AS ObjectTypeDesc
, SIdx.name AS IndexName
, SIdx.type AS IndexType
, SIdx.type_desc AS IndexTypeDesc
, SIdx.is_primary_key AS IsIndexAPrimaryKey
, SIdxCol.index_column_id AS IndexColumnSequence
, SCol.name AS IndexColumnName
FROM
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:

Advertisements

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:

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