These two queries may be helpful when troubleshooting systems that are running slower than they should. They will give a list of the table indexes on a dataset to help determine if any indexes are missing.

None. This is a selection query and no data is modified in the running of it.

This query will list all of the indexes in the database.

SELECT   Tab.name AS TableName, Ind.name AS IndexName, Ind.is_primary_key IsPrimary, 
         Ind.type_desc IndexType, Ind.is_unique IsUnique, 
         SUBSTRING( ( SELECT   ', ' + AC.name 
                      FROM     sys.tables AS T
                               JOIN sys.indexes I ON T.object_id = I.object_id
                               JOIN sys.index_columns IC ON I.object_id = IC.object_id AND I.index_id = IC.index_id
                               JOIN sys.all_columns AC ON T.object_id = AC.object_id AND IC.column_id = AC.column_id
                      WHERE    Ind.object_id = I.object_id AND Ind.index_id = I.index_id AND IC.is_included_column = 0
                      ORDER BY IC.key_ordinal
                      FOR XML PATH('') ), 2, 8000 ) AS KeyCols,
 
         SUBSTRING( ( SELECT   ', ' + AC.name
                      FROM     sys.tables AS T
                               JOIN sys.indexes I ON T.object_id = I.object_id
                               JOIN sys.index_columns IC ON I.object_id = IC.object_id AND I.index_id = IC.index_id
                               JOIN sys.all_columns AC ON T.object_id = AC.object_id AND IC.column_id = AC.column_id
                      WHERE    Ind.object_id = I.object_id AND Ind.index_id = I.index_id AND IC.is_included_column = 1
                      ORDER BY IC.key_ordinal
                      FOR XML PATH('') ), 2, 8000 ) AS IncludeCols
FROM     sys.indexes Ind 
         JOIN sys.tables Tab ON Tab.object_id = Ind.object_id
 
ORDER BY TableName, IsPrimary DESC, IndexName

This query will list all of the tables in the database and their primary key. If the table does not have a primary key, it will have NULL as it's primary key name and it will be sorted at the top of the results.

SELECT   Tab.name AS TableName,
         Ind.name AS PrimaryKeyName, 
         SUBSTRING( ( SELECT   ', ' + AC.name 
                      FROM     sys.tables AS T
                               JOIN sys.indexes I ON T.object_id = I.object_id
                               JOIN sys.index_columns IC ON I.object_id = IC.object_id AND I.index_id = IC.index_id
                               JOIN sys.all_columns AC ON T.object_id = AC.object_id AND IC.column_id = AC.column_id
                      WHERE    Ind.object_id = I.object_id AND Ind.index_id = I.index_id AND IC.is_included_column = 0
                      ORDER BY IC.key_ordinal
                      FOR XML PATH('') ), 2, 8000 ) AS KeyCols
FROM     sys.tables Tab
         LEFT JOIN sys.indexes Ind ON Ind.object_id = Tab.object_id AND Ind.is_primary_key = 1
ORDER BY CASE WHEN Ind.name IS NULL THEN 0 ELSE 1 END, TableName

• Entered : 03/06/2012
• Version : 1.0