Home > Handy Scripts > List all indexes on table(s)/column(s)

List all indexes on table(s)/column(s)

select i.name as index_name, i.type_desc as index_type, t.name as table_name, c.name as column_name, ic.index_column_id, ic.key_ordinal
from sys.indexes i 
	 inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
	 inner join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
	 inner join sys.tables t on t.object_id = ic.object_id and t.object_id = c.object_id
and t.name = [TABLE]
and c.name = [COLUMN];

OR (all tables in a database)

select 
    ind.name as index_name
    , t.name as table_name
    , ind.index_id
    , ic.index_column_id
    , col.name
    , ind.*, ic.*, col.* 
from sys.indexes ind 
	 inner join sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id 
	 inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id  
	 inner join sys.tables t on ind.object_id = t.object_id 
where ind.is_primary_key = 0  
	  and ind.is_unique = 0  
      and ind.is_unique_constraint = 0 
      and t.is_ms_shipped = 0 
order by t.name, ind.name, ind.index_id, ic.index_column_id;

ALTERNATIVELY:

SELECT 
  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, 
  (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK, 
  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ 
  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  '' as 'Type', 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6 
FROM sys.indexes as si 
LEFT JOIN sys.objects as so on so.object_id=si.object_id 
WHERE index_id>0 -- omit the default heap 
  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables 
  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams 
ORDER BY SchemaName,TableName,IndexName

 

Advertisements
Categories: Handy Scripts Tags:
  1. No comments yet.
  1. No trackbacks yet.

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: