Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_sql_server_table_indexes [2019/01/27 11:29]
 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.  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
 +
 +
 +