======  ======



===== Explanation of SQL =====



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.



===== Risk of Data Corruption if Run Improperly =====

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



===== SQL =====



===List of all indexes===



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



<code sql>
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
</code>



===List of tables with their primary keys===



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.



<code sql>
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
</code>



===== Version Information =====
  * Entered : 03/06/2012
  * Version : 1.0