Differences
This shows you the differences between two versions of the page.
— |
control_sql_-_sql_server_table_indexes [2019/01/27 11:29] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== | ||
+ | |||
+ | |||
+ | |||
+ | ===== Explanation of SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | These two queries may be helpful when troubleshooting systems that are running slower than they should. | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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 | ||
+ | | ||
+ | | ||
+ | FROM | ||
+ | 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('' | ||
+ | |||
+ | | ||
+ | FROM | ||
+ | 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('' | ||
+ | FROM | ||
+ | JOIN sys.tables Tab ON Tab.object_id = Ind.object_id | ||
+ | |||
+ | ORDER BY TableName, IsPrimary DESC, IndexName | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===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 | ||
+ | | ||
+ | | ||
+ | FROM | ||
+ | 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('' | ||
+ | FROM | ||
+ | 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 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 03/06/2012 | ||
+ | * Version : 1.0 | ||
+ | |||
+ | |||
+ | |||