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 icWHERE ic.Object_ID=i.Object_IDAND ic.index_ID=i.index_IDORDER BY index_column_ID ASCFOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') +' )'FROM sys.indexes iWHERE i.object_ID=t.object_IDFOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,''),'') AS Index_NameFROM sys.tables tORDER BYTable_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_NameFROM sys.tables tINNER JOIN sys.indexes iON t.object_ID = i.object_IDINNER JOIN sys.Index_columns icON i.Object_ID = ic.Object_IDAND i.index_ID = ic.index_IDORDER BYTable_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_DuplicatesFROM(SELECTObject_ID,name,STUFF(--get a list of columns (SELECT ', ' + COL_NAME(sc.Object_Id, sc.Column_Id)FROM sys.stats_columns scWHERE sc.Object_ID = s.Object_IDAND sc.stats_ID = s.stats_IDORDER BY stats_column_ID ASCFOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS ColumnListFROM sys.stats s) AS fGROUP BYObject_ID,ColumnListHAVING 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_NameFROM sys.indexes AS iINNER JOIN sys.tables t --to ensure only user tables are retrieved ON t.object_id=i.object_idWHERE is_hypothetical = 0AND i.index_id <> 0 --exclude unindexed (heap) table lines ORDER BYOBJECT_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_MBFROM sys.indexes iINNER JOIN sys.partitions pON i.object_id = p.object_idAND i.index_id = p.index_idINNER JOIN sys.allocation_units aON p.partition_id = a.container_idWHERE objectproperty(i.object_id, 'IsUserTable') = 1GROUP BY i.object_id, i.index_id, i.nameORDER BYTable_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.nameFROM sys.indexes i2WHERE t.object_ID = i2.object_IDORDER BY i2.nameFOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_ListFROM sys.tables AS tLEFT OUTER JOIN sys.indexes iON t.object_id = i.object_idAND is_hypothetical = 0AND i.index_id > 0GROUP BY t.Object_IDORDER BYOBJECT_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_NameFROM sys.stats sINNER JOIN sys.stats_columns scON s.object_id = sc.object_idAND s.stats_id = sc.stats_idINNER JOIN sys.columns cON sc.object_id = c.object_idAND sc.column_id = c.column_idINNER JOIN sys.tables tON s.object_id = t.object_idORDER BYTable_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_IndexesFROM(SELECTCOUNT(*) AS Index_Count,t.object_IDFROM sys.indexes iINNER JOIN sys.tables tON i.object_ID=t.object_IDGROUP BY t.object_ID) AS TheIndexesINNER JOIN(SELECTCOUNT(*) AS Column_Count,t.object_IDFROM sys.columns cINNER JOIN sys.tables tON c.object_ID=t.object_IDGROUP BY t.object_ID) AS TheColumnsON TheIndexes.object_ID = TheColumns.object_IDWHERE Index_Count > Column_Count/2OR Index_Count > 4ORDER 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_NameFROM sys.indexes iLEFT OUTER JOIN sys.dm_db_index_usage_stats sON s.object_id = i.object_idAND s.index_id = i.index_idAND s.database_id = DB_ID()INNER JOIN sys.tables t --to ensure only user tables are retrieved ON t.object_id=i.object_idWHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1AND 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 BYTable_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_NameFROM sys.indexes i /* see whether the table is a heap */INNER JOIN sys.tables tON t.object_ID=i.object_IDWHERE i.type = 0ORDER BYOBJECT_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((SELECTSUM(s.rows)FROM sys.partitions sWHERE s.object_id = i.object_idAND s.index_id = i.index_ID), 0) AS Index_Row_Count,COALESCE(ep.Value,'') AS DocumentationFROM sys.tables tINNER JOIN sys.indexes i ON i.object_id = t.object_idLEFT OUTER JOIN sys.Extended_Properties epON i.Object_Id = ep.Major_IdAND i.Index_Id = Minor_IdAND Class = 7ORDER BYOBJECT_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+ CASEWHEN 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 BuildScriptFROM sys.xml_Indexes xiINNER JOIN sys.index_columns icON ic.Index_Id = xi.Index_IdAND ic.Object_Id = xi.Object_IdLEFT OUTER JOIN sys.Indexes [USING]ON [USING].Index_Id = xi.UsIng_xml_Index_IdAND [USING].Object_Id = xi.Object_IdLEFT OUTER JOIN sys.Extended_Properties epON ic.Object_Id = ep.Major_IdAND ic.Index_Id = Minor_IdAND Class = 7WHERE OBJECT_SCHEMA_NAME(ic.Object_ID) <>'sys'AND ic.index_id > 0