SQL Server - Index list

Compatible from SQL Server 2008

Get information about all existing indexes in given database. This script can be handful when you want to check all indexes with their create scripts and some of useful information about user statistics, basic configuration, etc. all of that in one table. You can process that data further in temp table and so on. Really handful and I’m using it almost in every performance investigation.

TIP: Use that if you want to recreate replication and you have some additional indexing on subscriber.

Script source: https://littlekendra.com/2016/05/05/how-to-script-out-indexes-from-sql-server/

SELECT 
	DB_NAME() AS database_name, 
	sc.name + N'.' + t.name AS table_name, 
	(
		SELECT MAX(user_reads)
		FROM (
			VALUES (last_user_seek), (last_user_scan), (last_user_lookup)
			) AS value(user_reads)
		) AS last_user_read, last_user_update, 
		'IF NOT EXISTS (SELECT *  FROM sys.indexes  WHERE name=''' + si.name + ''' AND object_id = OBJECT_ID(''' + sc.name + '.' + t.name + '''))' AS checking_statement, CASE si.index_id
		WHEN 0
			THEN N'/* No create statement (Heap) */'
		ELSE 
			CASE is_primary_key
				WHEN 1 THEN N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' + CASE 
				WHEN si.index_id > 1 THEN N'NON'
				ELSE N''
			END + N'CLUSTERED '
		ELSE N'CREATE ' + 
			CASE 
				WHEN si.is_unique = 1 THEN N'UNIQUE '
				ELSE N''
			END + 
			CASE 
				WHEN si.index_id > 1 THEN N'NON'
				ELSE N''
			END + 
			N'CLUSTERED ' + N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
		END +
		/* key def */ N'(' + key_definition + N')' +
		/* includes */ 
		CASE 
			WHEN include_definition IS NOT NULL THEN N' INCLUDE (' + include_definition + N')'
			ELSE N''
		END +
		/* filters */ 
		CASE 
			WHEN filter_definition IS NOT NULL THEN N' WHERE ' + filter_definition
			ELSE N''
		END +
		/* with clause - compression goes here */
		CASE 
			WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL THEN N' WITH (' + 
				CASE 
					WHEN row_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = ROW ' + 
						CASE 
							WHEN psc.name IS NULL THEN N''
							ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')'
						END
					ELSE N''
				END + 
				CASE 
					WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', '
					ELSE N''
				END + 
				CASE 
					WHEN page_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = PAGE ' + 
					CASE 
						WHEN psc.name IS NULL THEN N''
						ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')'
					END
					ELSE N''
				END + N')'
			ELSE N''
		END +
		/* ON where? filegroup? partition scheme? */
		' ON ' + 
		CASE 
			WHEN psc.name IS NULL THEN ISNULL(QUOTENAME(fg.name), N'')
			ELSE psc.name + N' (' + partitioning_column.column_name + N')'
		END + N';'
	END AS index_create_statement, 
	si.index_id, 
	si.name AS index_name, 
	partition_sums.reserved_in_row_GB, 
	partition_sums.reserved_LOB_GB, 
	partition_sums.row_count, 
	stat.user_seeks, 
	stat.user_scans, 
	stat.user_lookups, 
	stat.user_updates, 
	partition_sums.partition_count, 
	si.allow_page_locks, 
	si.allow_row_locks, 
	si.is_hypothetical, 
	si.has_filter, 
	si.fill_factor, 
	si.is_unique, 
	ISNULL(pf.name, '/* Not partitioned */') AS partition_function, 
	ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup, 
	t.create_date AS table_created_date, 
	t.modify_date AS table_modified_date
FROM sys.indexes AS si
	INNER JOIN sys.tables AS t ON si.object_id = t.object_id
	INNER JOIN sys.schemas AS sc ON t.schema_id = sc.schema_id
	LEFT JOIN sys.dm_db_index_usage_stats AS stat ON stat.database_id = DB_ID() AND si.object_id = stat.object_id AND si.index_id = stat.index_id
	LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id = psc.data_space_id
	LEFT JOIN sys.partition_functions AS pf ON psc.function_id = pf.function_id
	LEFT JOIN sys.filegroups AS fg ON si.data_space_id = fg.data_space_id
	/* Key list */
	OUTER APPLY (
		SELECT 
			STUFF((
				SELECT N', ' + QUOTENAME(c.name) + CASE ic.is_descending_key
					WHEN 1 THEN N' DESC' ELSE N'' END
				FROM sys.index_columns AS ic
					INNER JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
				WHERE ic.object_id = si.object_id 
					AND ic.index_id = si.index_id 
					AND ic.key_ordinal > 0
				ORDER BY ic.key_ordinal
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
		) AS keys(key_definition)
	/* Partitioning Ordinal */
	OUTER APPLY (
		SELECT 
			MAX(QUOTENAME(c.name)) AS column_name
		FROM sys.index_columns AS ic
			INNER JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
		WHERE ic.object_id = si.object_id 
			AND ic.index_id = si.index_id 
			AND ic.partition_ordinal = 1
		) AS partitioning_column
	/* Include list */
	OUTER APPLY (
		SELECT 
			STUFF((
				SELECT 
					N', ' + QUOTENAME(c.name)
				FROM sys.index_columns AS ic
					INNER JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
				WHERE ic.object_id = si.object_id 
					AND ic.index_id = si.index_id AND ic.is_included_column = 1
				ORDER BY c.name
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
		) AS includes(include_definition)
	/* Partitions */
	OUTER APPLY (
		SELECT 
			COUNT(*) AS partition_count, 
			CAST(SUM(ps.in_row_reserved_page_count) * 8. / 1024. / 1024. AS NUMERIC(32, 1)) AS reserved_in_row_GB, 
			CAST(SUM(ps.lob_reserved_page_count) * 8. / 1024. / 1024. AS NUMERIC(32, 1)) AS reserved_LOB_GB, 
			SUM(ps.row_count) AS row_count
		FROM sys.partitions AS p
			INNER JOIN sys.dm_db_partition_stats AS ps ON p.partition_id = ps.partition_id
		WHERE p.object_id = si.object_id 
			AND p.index_id = si.index_id
		) AS partition_sums
	/* row compression list by partition */
	OUTER APPLY (
		SELECT 
			STUFF((
				SELECT 
					N', ' + CAST(p.partition_number AS VARCHAR(32))
				FROM sys.partitions AS p
				WHERE p.object_id = si.object_id 
					AND p.index_id = si.index_id 
					AND p.data_compression = 1
				ORDER BY p.partition_number
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
		) AS row_compression_clause(row_compression_partition_list)
	/* data compression list by partition */
	OUTER APPLY (
		SELECT 
			STUFF((
				SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
				FROM sys.partitions AS p
				WHERE p.object_id = si.object_id 
					AND p.index_id = si.index_id 
					AND p.data_compression = 2
				ORDER BY p.partition_number
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
		) AS page_compression_clause(page_compression_partition_list)
WHERE si.type IN (0, 1, 2) /* heap, clustered, nonclustered */
	AND si.index_id NOT IN (0, 1)
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
database_namesysnameName of actual database
table_namesysnameTable name with schema name where index is created in
last_user_readdatetimeTime of last user seek, scan or lookup
last_user_updatedatetimeTime of last user update.
checking_statementnvarchar(MAX)T-SQL statement that can be used when recreating this index. You can check if index already exists.
index_create_statementnvarchar(MAX)T-SQL statement that can be used to create this index
index_idintID of the index. index_id is unique only within the object. 0 = Heap, 1 = Clustered index, > 1 = Nonclustered index.
index_namesysnameName of the index. name is unique only within the object. NULL = Heap.
reserved_in_row_GBbigintTotal GB size of pages reserved for storing and managing in-row data in this partition, regardless of whether the pages are in use or not. Always 0 for a columnstore index.
reserved_LOB_GBbigintTotal GB size of pages reserved for storing and managing out-of-row text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml columns within the partition, regardless of whether the pages are in use or not. IAM pages are included. Total number of LOBs reserved for storing and managing a columnstore index in the partition.
row_countbigintThe approximate number of rows in the partition.
user_seeksbigintNumber of seeks by user queries.
user_scansbigintNumber of scans by user queries that did not use 'seek' predicate.
user_lookupsbigintNumber of bookmark lookups by user queries.
user_updatesbigintNumber of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1
partition_countbigintNumber of partitions of index is partitioned.
allow_page_locksbit1 = Index allows page locks. 0 = Index does not allow page locks. Always 0 for clustered columnstore indexes.
allow_row_locksbit1 = Index allows row locks. 0 = Index does not allow row locks. Always 0 for clustered columnstore indexes.
is_hypotheticalbit1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics. 0 = Index is not hypothetical.
has_filterbit1 = Index has a filter and only contains rows that satisfy the filter definition. 0 = Index does not have a filter.
fill_factortinyint> 0 = FILLFACTOR percentage used when the index was created or rebuilt. 0 = Default value Always 0 for clustered columnstore indexes.
is_uniquebit1 = Index is unique. 0 = Index is not unique. Always 0 for clustered columnstore indexes.
partition_functionsysnameName of the partition function. Is unique within the database.
partition_scheme_or_filegroupsysnameName of data space, unique within the database.
table_created_datedatetimeDate the table object was created.
table_modified_datedatetimeDate the table object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.

Leave a Reply

Your email address will not be published. Required fields are marked *