question

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, 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, user_updates AS queries_that_modified, 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_modify_date FROM sys.indexes AS si JOIN sys.tables AS t ON si.object_id = t.object_id 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 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 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 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 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.NAME NOT LIKE '%PK%' AND t.NAME NOT LIKE '%dbo%' ORDER BY table_name, si.index_id OPTION (RECOMPILE); GO