Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== 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