DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to list all indexes in a database?

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?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE