Tags: Database Index , MSSQL

I was playing with MSSQL system tables for fun and popup one thing that lets try to find all indexes on column. I ended up with one long query so thought let me post it and you guys don't have work for this query again.

Professionals I don't have to explain much about these tables. Beginner try to work around this table. It'll help you lot in future, way to become database expert in other word Database Administrator(DBA).

/* Change table name and column name in where condition */
SELECT distinct ind.name AS [idx_name], t.name AS [table_name], c.name AS [column_name], ic.is_included_column
FROM sys.indexes ind 
RIGHT JOIN sys.tables t 
 ON ind.object_id = t.object_id 
RIGHT JOIN sys.columns c 
 ON t.object_id = c.object_id
RIGHT JOIN sys.index_columns ic 
 ON ind.index_id = ic.index_id
       AND c.column_id = ic.column_id
WHERE ind.is_primary_key = 0  
  AND ind.is_unique_constraint = 0 
  AND t.is_ms_shipped = 0
  AND ind.name IS NOT NULL
  AND ind.is_unique IN (0,1)
  AND t.name = 'table_name'
  AND c.name = 'column_name'
  AND OBJECT_NAME(ic.object_id) = 'table_name'

There ain't much to write for this query. Change table_name and column_name. 

Tags: Database Index , MSSQL