Enhanced Indexes List

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Enhanced Indexes List

It is often useful to query the list of all indexes in a database. Such a report can help in tuning the database or in designing a new feature.

One way to achieve this is to use the sys.sp_helpindex system stored procedure. This stored procedure displays all the indexes for a specific table or view. It displays the information about each index in a single description, which is not so convenient if you want to manipulate the data in some way. It also displays a list of the index keys, but it does not display the included columns. And if you want to generate a list of the indexes in the whole database, you’ll have to execute this stored procedure in a loop for every table or view.


The alternative is, of course, to query the system views and functions. There are several catalog views and dynamic management views that store relevant information, and you need to know the details in order to write the query correctly. Here is a query that will save you some time.


The query displays the following information for all the indexes in the current database. It displays the information for every partition of every index.


  • ID and name of the indexIndex type (clustered or non-clustered)

  • Constraint type (primary key, unique constraint, unique index or none)

  • List of index keys

  • List of included columns, if any

  • Partition number

  • Number of rows and number of pages

  • Index fragmentation level

  • Index fill factorUsage level (low, high or none) - Consider dropping or altering indexes with a low or none usage


SELECT
	SchemaName		= SCHEMA_NAME (Tables.schema_id) ,
	TableName		= Tables.name ,
	IndexId			= Indexes.index_id ,
	IndexName		= Indexes.name ,
	IndexType		= Indexes.type_desc ,
	ConstraintType	=	CASE
							WHEN Indexes.is_primary_key = 1			THEN N'Primary Key'
							WHEN Indexes.is_unique_constraint = 1	THEN N'Unique Constraint'
							WHEN Indexes.is_unique = 1				THEN N'Unique Index'
							ELSE N''
						END ,
	IndexKeys		= IndexKeys.IndexKeysList ,
	IncludedColumns	= IncludedColumns.IncludedColumnsList ,
	PartitionNumber	= IndexPhysicalStats.partition_number ,
	NumberOfRows	= Partitions.rows ,
	NumberOfPages	= IndexPhysicalStats.page_count ,
	Fragmentation	= IndexPhysicalStats.avg_fragmentation_in_percent ,
	IndexFillFactor	= Indexes.fill_factor ,
	UsageLevel		=	CASE
							WHEN ISNULL (IndexUsageStats.user_seeks , 0) + ISNULL (IndexUsageStats.user_scans , 0) + ISNULL (IndexUsageStats.user_lookups , 0) + ISNULL (IndexUsageStats.user_updates , 0) = 0
								THEN N'None'
							WHEN CAST ((ISNULL (IndexUsageStats.user_seeks , 0) + ISNULL (IndexUsageStats.user_scans , 0) + ISNULL (IndexUsageStats.user_lookups , 0)) AS DECIMAL(19,2)) / CAST ((ISNULL (IndexUsageStats.user_seeks , 0) + ISNULL (IndexUsageStats.user_scans , 0) + ISNULL (IndexUsageStats.user_lookups , 0) + ISNULL (IndexUsageStats.user_updates , 0)) AS DECIMAL(19,2)) <= 0.1
								THEN N'Low'
							ELSE
								N'High'
						END
FROM
	sys.tables AS Tables
INNER JOIN
	sys.indexes AS Indexes
ON
	Tables.object_id = Indexes.object_id
CROSS APPLY
	(
		SELECT
			REPLACE
			(
				REPLACE
				(
					REPLACE
					(
						(
							SELECT
								Columns.name AS c
							FROM
								sys.index_columns AS IndexColumns
							INNER JOIN
								sys.columns AS Columns
							ON
								IndexColumns.object_id = Columns.object_id
							AND
								IndexColumns.column_id = Columns.column_id
							WHERE
								IndexColumns.object_id = Indexes.object_id
							AND
								IndexColumns.index_id = Indexes.index_id
							AND
								IndexColumns.is_included_column = 0
							ORDER BY
								IndexColumns.key_ordinal ASC
							FOR XML PATH (N'')
						) ,
						N'</c><c>' ,
						N' , '
					) ,
					N'<c>' ,
					N''
				) ,
				N'</c>' ,
				N''
			)
			AS IndexKeysList
	)
	AS IndexKeys
CROSS APPLY
	(
		SELECT
			REPLACE
			(
				REPLACE
				(
					REPLACE
					(
						(
							SELECT
								Columns.name AS c
							FROM
								sys.index_columns AS IndexColumns
							INNER JOIN
								sys.columns AS Columns
							ON
								IndexColumns.object_id = Columns.object_id
							AND
								IndexColumns.column_id = Columns.column_id
							WHERE
								IndexColumns.object_id = Indexes.object_id
							AND
								IndexColumns.index_id = Indexes.index_id
							AND
								IndexColumns.is_included_column = 1
							ORDER BY
								IndexColumns.key_ordinal ASC
							FOR XML PATH (N'')
						) ,
						N'</c><c>' ,
						N' , '
					) ,
					N'<c>' ,
					N''
				) ,
				N'</c>' ,
				N''
			)
			AS IncludedColumnsList
	)
	AS IncludedColumns
INNER JOIN
	sys.partitions AS Partitions
ON
	Indexes.object_id = Partitions.object_id
AND
	Indexes.index_id = Partitions.index_id
INNER JOIN
	sys.dm_db_index_physical_stats (DB_ID () , NULL , NULL , NULL , N'LIMITED') AS IndexPhysicalStats
ON
	Partitions.object_id = IndexPhysicalStats.object_id
AND
	Partitions.index_id = IndexPhysicalStats.index_id
AND
	Partitions.partition_number = IndexPhysicalStats.partition_number
LEFT OUTER JOIN
	sys.dm_db_index_usage_stats AS IndexUsageStats
ON
	Indexes.object_id = IndexUsageStats.object_id
AND
	Indexes.index_id = IndexUsageStats.index_id
WHERE
	Indexes.is_hypothetical = 0
AND
	Indexes.index_id > 0
ORDER BY
	SchemaName	ASC ,
	TableName	ASC ,
	IndexId		ASC;



Good luck!

This blog was initially published at our Linkedin blog

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle