Execute the following Microsoft SQL Server T-SQL script for listing index metadata including index columns, index names, unique and clustered properties. Ordinal = 0 means INCLUDEd non-key column.
-- SQL Server list all indexes in database - SQL Server database schema USE AdventureWorks2008; 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; /* SchemaName TableName ColumnName Unique Clustered Ordinal IndexName .... Production Product Color No NC 0 idxProdInclude Production Product ListPrice No NC 0 idxProdInclude Production Produc ProductNumber No NC 1 idxProdInclude Production Product Name No NC 2 idxProdInclude ....*/ ------------
-- List all indexes in single row with column list
USE AdventureWorks2008;
SELECT DISTINCT SchemaName = schema_name(schema_id),
TableName = object_name(t.object_id),
ColumnList = Stuff((SELECT ', ' + c1.Name AS [text()]
FROM sys.indexes i1
INNER JOIN sys.tables t1
ON i1.object_id = t1.object_id
INNER JOIN sys.index_columns ic1
ON ic1.object_id = t1.object_id
AND ic1.index_id = i1.index_id
INNER JOIN sys.columns c1
ON c1.object_id = t1.object_id
AND ic1.column_id = c1.column_id
WHERE t.schema_id = t1.schema_id
and t.object_id = t1.object_id
and i.name =i1.name
FOR XML PATH ('')),1,1,''),
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
/* Partial results
SchemaName TableName ColumnList
Production ProductDocument ProductID, DocumentNode
Production ProductInventory ProductID, LocationID
Production ProductListPriceHistory ProductID, StartDate
*/ ------------
Relates articles:
SQL Server 2005: Script all Indexes
How to create a covering index?
|