Get Index Metadata

--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

--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 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;

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 hte schema - allows you to spot tables with multiple schemas

,Index_Name

-- 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

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

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

--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)

--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

-- 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)

--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)')

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