Cool Tips‎ > ‎Indexes‎ > ‎

Get Index Metadata

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested        

Credits:

Author:Phil Factor/ChillyDBA
Date:    4 Jun 2020

Description

  A small collection of queries to produce database-level reports on indexes.  These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:
  1. Summary List - containing schema/object names and any stored extended properties.  Ideal for documentation
  2. Detail List - containing schema/object names plus object type, component columns and code.  Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries
Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views.  This is generally for Detail type queries as some detail levels are only available in one source

Code

Get all Indexes with Columns - Pivoted:

 --list all tables with indexes, listing each index (comma-delimited), with a --list of their columns in brackets.

SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name ,COALESCE(STUFF (--get a list of indexes (SELECT ', '+i.name +' ( ' + STUFF (--get a list of columns (SELECT ', ' + COL_NAME(ic.Object_Id, ic.Column_Id) FROM sys.Index_columns ic WHERE ic.Object_ID=i.Object_ID AND ic.index_ID=i.index_ID ORDER BY index_column_ID ASC FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') +' )' FROM sys.indexes i WHERE i.object_ID=t.object_ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,''),'') AS Index_Name FROM sys.tables t ORDER BY Table_Name ,Index_Name


Get all Indexes with Columns - Detail List:

 --find all the columns for all the indexes for every table 

SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name ,i.name AS Index_Name ,ic.index_column_id AS Index_Column_Order ,COL_NAME(ic.Object_Id, ic.Column_Id) AS Index_Column_Name FROM sys.tables t INNER JOIN sys.indexes i ON t.object_ID = i.object_ID INNER JOIN sys.Index_columns ic ON i.Object_ID = ic.Object_ID AND i.index_ID = ic.index_ID ORDER BY Table_Name ,Index_Name ,Index_Column_Order


Get all Columns having duplicate or similar statistics:

 -- get columns with similar or duplicate statistics 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(f.object_ID) AS Schema_Name ,OBJECT_NAME(f.object_ID) AS Table_Name ,CONVERT(CHAR(50),OBJECT_SCHEMA_NAME(f.object_ID)+'.' + OBJECT_NAME(f.object_ID)) AS Combined_Name ,COUNT(*) AS Statistics_Similar ,ColumnList AS Statistics_Column ,MAX(name) + ', ' + MIN(name) AS StatBBBBt_Duplicates FROM ( SELECT Object_ID ,name ,STUFF (--get a list of columns ( SELECT ', ' + COL_NAME(sc.Object_Id, sc.Column_Id) FROM sys.stats_columns sc WHERE sc.Object_ID = s.Object_ID AND sc.stats_ID = s.stats_ID ORDER BY stats_column_ID ASC FOR XML PATH(''), TYPE ).value('.', 'varchar(max)') ,1,2,'' ) AS ColumnList FROM sys.stats s ) AS f GROUP BY Object_ID ,ColumnList HAVING COUNT(*) >1;


Get all Indexes Summary:

 SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,CONVERT(CHAR(50),OBJECT_SCHEMA_NAME(t.object_ID)+'.' + OBJECT_NAME(t.object_ID)) AS Combined_Name ,i.name AS Index_Name FROM sys.indexes AS i INNER JOIN sys.tables t --to ensure only user tables are retrieved ON t.object_id=i.object_id WHERE is_hypothetical = 0 AND i.index_id <> 0 --exclude unindexed (heap) table lines ORDER BY OBJECT_NAME(t.object_ID) --order by the name only and not the schema - allows you to spot tables with multiple schemas ,Index_Name


Get all Indexes with Size in MB

 -- how much space are indexes consuming 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(i.object_ID) AS Schema_Name ,OBJECT_NAME(i.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(i.object_id) +'.' + OBJECT_NAME(i.object_id) AS Combined_Name ,COALESCE(i.name,'heap IAM') AS Index_Name ,CONVERT(DECIMAL(9,2),(SUM(a.total_pages) * 8.00) / 1024.00) AS Index_Size_MB FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE objectproperty(i.object_id, 'IsUserTable') = 1 GROUP BY i.object_id, i.index_id, i.name ORDER BY Table_Name ,Index_Name


Get all Indexes per table with count and index names - Pivoted:

 SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,CONVERT(CHAR(50),OBJECT_SCHEMA_NAME(t.object_ID)+'.' + OBJECT_NAME(t.object_ID)) AS Combined_Name ,SUM(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS Index_Count ,COALESCE ( STUFF ( ( SELECT ', ' + i2.name FROM sys.indexes i2 WHERE t.object_ID = i2.object_ID ORDER BY i2.name FOR XML PATH(''), TYPE ).value(N'(./text())[1]',N'varchar(8000)'),1,2,'' ),'' ) AS Index_List FROM sys.tables AS t LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id AND is_hypothetical = 0 AND i.index_id > 0 GROUP BY t.Object_ID ORDER BY OBJECT_NAME(t.object_ID) --order by the name only and not the schema - allows you to spot tables with multiple schemas


Get summary of all Indexes with associated Index Statistics:

 SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,CONVERT(CHAR(50),OBJECT_SCHEMA_NAME(t.object_ID)+'.' + OBJECT_NAME(t.object_ID)) AS Combined_Name ,c.name AS Column_Name ,s.name AS Stats_Name FROM sys.stats s INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id INNER JOIN sys.tables t ON s.object_id = t.object_id ORDER BY Table_Name ,Column_Name


Get all Tables in a Database with excessive indexes:

 --tables with more than three indexes --or an index count greater than half the column count 

SELECT

OBJECT_SCHEMA_NAME(TheIndexes.object_ID) AS Schema_Name ,OBJECT_NAME(TheIndexes.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(TheIndexes.object_id) +'.' + OBJECT_NAME(TheIndexes.object_id) AS Combined_Name ,Column_Count ,Index_Count , CASE WHEN Index_Count > Column_Count/2 THEN 1 ELSE 0 END AS Excessive_Index_To_Column_Ratio , CASE WHEN Index_Count > 4 THEN 1 ELSE 0 END AS Excessive_Indexes FROM ( SELECT COUNT(*) AS Index_Count ,t.object_ID FROM sys.indexes i INNER JOIN sys.tables t ON i.object_ID=t.object_ID GROUP BY t.object_ID ) AS TheIndexes INNER JOIN ( SELECT COUNT(*) AS Column_Count ,t.object_ID FROM sys.columns c INNER JOIN sys.tables t ON c.object_ID=t.object_ID GROUP BY t.object_ID ) AS TheColumns ON TheIndexes.object_ID = TheColumns.object_ID WHERE Index_Count > Column_Count/2 OR Index_Count > 4 ORDER BY OBJECT_NAME(TheIndexes.object_ID)


Get all unused indexes within a Database:

 --Indexes updated but not read. ie NOT USED 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name ,i.name AS Index_Name FROM sys.indexes i LEFT OUTER JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID() INNER JOIN sys.tables t --to ensure only user tables are retrieved ON t.object_id=i.object_id WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.index_id > 0 --Exclude heaps. AND i.is_primary_key = 0 --and Exclude primary keys. AND i.is_unique = 0 --and Exclude unique constraints. AND COALESCE(s.user_lookups + s.user_scans + s.user_seeks, 0) = 0 --No user reads. AND COALESCE(s.user_updates, 0) > 0 --Index is being updated. ORDER BY Table_Name ,Index_Name


Get all Tables with no Clustered Index (ie Heaps):

 -- table without a clustered index is called a 'Heap' as it is just a Heap of unordered data on disk 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Heap_Table_Name ,OBJECT_SCHEMA_NAME(t.object_id) +'.' + OBJECT_NAME(t.object_id) AS Combined_Name FROM sys.indexes i /* see whether the table is a heap */ INNER JOIN sys.tables t ON t.object_ID=i.object_ID WHERE i.type = 0 ORDER BY OBJECT_NAME(t.object_ID)


Get all Indexes with RowCounts and Extended Properties:

 --list the number of rows for each index/heap 

SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name ,OBJECT_NAME(t.object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) AS Combined_Name ,COALESCE(i.NAME,'(IAM for heap)') AS Index_Name ,COALESCE ( ( SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id AND s.index_id = i.index_ID ), 0 ) AS Index_Row_Count ,COALESCE(ep.Value,'') AS Documentation FROM sys.tables t INNER JOIN sys.indexes i ON i.object_id = t.object_id LEFT OUTER JOIN sys.Extended_Properties ep ON i.Object_Id = ep.Major_Id AND i.Index_Id = Minor_Id AND Class = 7 ORDER BY OBJECT_NAME(t.object_ID) --order by the name only and not the schema - allows you to spot tables with multiple schemas ,COALESCE(i.NAME,'(IAM for heap)')


Get all XML Indexes with Create statements:

 SELECT

'CREATE' + CASE WHEN secondary_type IS NULL THEN ' PRIMARY' ELSE '' END + ' XML INDEX ' + COALESCE(xi.name,'') + ' ON ' --what table and column is this XML index on? + OBJECT_SCHEMA_NAME(ic.Object_ID) + '.' + OBJECT_NAME(ic.Object_ID) + ' (' + COL_NAME(ic.Object_Id, ic.Column_Id) + ' ) ' + COALESCE('USING XML INDEX [' + Using.Name + '] FOR ' + Secondary_Type_DeSc COLLATE database_default,'') + ' ' + REPLACE('WITH ( ' + STUFF( CASE WHEN xi.Is_Padded <> 0 THEN ', PAD_INDEX = ON ' ELSE '' END + CASE WHEN xi.Fill_Factor NOT IN (0, 100) THEN ', FILLFACTOR =' + convert(VARCHAR(3), xi.Fill_Factor) + '' ELSE '' END + CASE WHEN xi.Ignore_dUp_Key <> 0 THEN ', IGNORE_DUP_KEY = ON' ELSE '' END + CASE WHEN xi.Allow_Row_Locks = 0 THEN ', ALLOW_ROW_LOCKS = OFF' ELSE '' END + CASE WHEN xi.Allow_Page_Locks = 0 THEN ', ALLOW_PAGE_LOCKS = OFF' ELSE ' ' END , 1, 1, '') + ')', 'WITH ( )', '') --create the list of xml index options + COALESCE('/* ' + CONVERT(VARCHAR(8000),VALUE)+ '*/','')--and any comment AS BuildScript FROM sys.xml_Indexes xi INNER JOIN sys.index_columns ic ON ic.Index_Id = xi.Index_Id AND ic.Object_Id = xi.Object_Id LEFT OUTER JOIN sys.Indexes [USING] ON [USING].Index_Id = xi.UsIng_xml_Index_Id AND [USING].Object_Id = xi.Object_Id LEFT OUTER JOIN sys.Extended_Properties ep ON ic.Object_Id = ep.Major_Id AND ic.Index_Id = Minor_Id AND Class = 7 WHERE OBJECT_SCHEMA_NAME(ic.Object_ID) <>'sys' AND ic.index_id > 0