MCITP

MCITP

Tuesday, September 11, 2012

Script to list all indexes in the databases.

Run the below script on the database and it will list down all the indexes present inside the database.



SELECT   SchemaName = schema_name(schema_id),
         TableName = object_name(t.object_id),
         ColumnName = c.name,
         [Unique] = CASE
                      WHEN i.is_unique = 1 THEN 'Yes'
                      ELSE 'No'
                    END,
         [Clustered] = CASE
                         WHEN i.index_id = 1 THEN 'C'
                         ELSE 'NC'
                       END,
         Ordinal = key_ordinal,
         IndexName = i.Name
FROM     sys.indexes i
         INNER JOIN sys.tables t
           ON i.object_id = t.object_id
         INNER JOIN sys.index_columns ic
           ON ic.object_id = t.object_id
              AND ic.index_id = i.index_id
         INNER JOIN sys.columns c
           ON c.object_id = t.object_id
              AND ic.column_id = c.column_id
ORDER BY SchemaName,
         TableName,
         IndexName,
         key_ordinal

No comments:

Post a Comment

Followers