Kendra Little has a gist to script out all indexes HOW TO SCRIPT OUT INDEXES FROM SQL SERVER but it didn’t include XML or columnstore indexes, so I’ve forked her gist and added a few things to it. I changed the FOR XML/STUFF trick into STRING_AGG() (which is SQL Server 2017 onwards) for no other reason than I’m not working with any instance versions less than that.
The updated gist is here.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /***************************************************************************** | |
| MIT License, http://www.opensource.org/licenses/mit-license.php | |
| Contact: help@sqlworkbooks.com | |
| Copyright (c) 2018 SQL Workbooks LLC | |
| Copyright (c) 2024 Mitch Wheat | |
| Permission is hereby granted, free of charge, to any person | |
| obtaining a copy of this software and associated documentation | |
| files (the "Software"), to deal in the Software without | |
| restriction, including without limitation the rights to use, | |
| copy, modify, merge, publish, distribute, sublicense, and/or | |
| sell copies of the Software, and to permit persons to whom | |
| the Software is furnished to do so, subject to the following | |
| conditions: | |
| The above copyright notice and this permission notice shall be | |
| included in all copies or substantial portions of the Software. | |
| THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, | |
| EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES | |
| OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND | |
| NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT | |
| HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, | |
| WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING | |
| FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR | |
| OTHER DEALINGS IN THE SOFTWARE. | |
| *****************************************************************************/ | |
| -- Removed FOR-XML 'trick' used to concatenate comma separated values with STRING_AGG() | |
| -- STRING_AGG() is SQL Server 2017 onwards... | |
| -- Added: | |
| -- is_autocreated | |
| -- xml indexes | |
| -- columnstore indexes | |
| -- Currently not showing: | |
| -- | |
| -- 1. xml compression by partition (SQL Server2022 onwards) | |
| -- 2. spatial indexes | |
| -- 3. selective xml indexes | |
| -- 4. NONCLUSTERED HASH indexes | |
| SELECT | |
| DB_NAME() AS [database], | |
| sc.name + N'.' + t.name AS [table], | |
| si.name AS [index], | |
| si.type_desc AS indextype, | |
| CASE si.index_id | |
| WHEN 0 THEN N'/* Heap */' | |
| ELSE | |
| CASE WHEN si.is_primary_key = 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 xi.using_xml_index_id IS NULL AND (si.type = 3) THEN N'PRIMARY ' ELSE N'' END + | |
| CASE | |
| WHEN si.type = 1 THEN N'CLUSTERED' | |
| WHEN si.type = 2 THEN N'NONCLUSTERED' | |
| WHEN si.type = 3 THEN N'XML' | |
| WHEN si.type = 5 THEN N'CLUSTERED COLUMNSTORE' | |
| WHEN si.type = 6 THEN N'NONCLUSTERED COLUMNSTORE' | |
| --WHEN si.type = 4 THEN N'SPATIAL' | |
| --WHEN si.type = 7 THEN N'NONCLUSTERED HASH' | |
| ELSE N'' | |
| END + | |
| N' INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N'' | |
| END + | |
| CASE | |
| WHEN key_column_list IS NOT NULL AND (si.type != 5) THEN '(' + key_column_list + N')' | |
| WHEN xmlkey_column_list IS NOT NULL AND (si.type= 3) THEN '(' + xmlkey_column_list + N')' | |
| WHEN include_column_list IS NOT NULL AND (si.type = 6) THEN '(' + include_column_list + N')' | |
| ELSE N'' | |
| END + | |
| CASE WHEN primary_name IS NOT NULL THEN N' USING XML INDEX ' + primary_name + N' FOR ' + xi.secondary_type_desc COLLATE Latin1_General_CI_AS ELSE N'' END + | |
| CASE WHEN include_column_list IS NOT NULL AND (si.type != 5 AND si.type != 6) THEN N' INCLUDE (' + include_column_list + N')' ELSE N'' END + | |
| CASE WHEN si.filter_definition IS NOT NULL THEN N' WHERE ' + si.filter_definition ELSE N'' END + | |
| /* with clause */ | |
| CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL OR columnstore_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 + | |
| CASE | |
| WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL AND columnstore_compression_partition_list IS NOT NULL THEN N', ' | |
| ELSE N'' | |
| END + | |
| CASE | |
| WHEN columnstore_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = COLUMNSTORE' + | |
| CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + columnstore_compression_partition_list + N')' END | |
| ELSE N'' | |
| END + | |
| CASE | |
| WHEN si.compression_delay IS NOT NULL THEN N', COMPRESSION_DELAY = ' + CAST(si.compression_delay AS NVARCHAR(16)) | |
| ELSE N'' | |
| END | |
| + N')' | |
| ELSE N'' | |
| END + | |
| CASE | |
| WHEN si.type != 3 THEN | |
| /* 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 | |
| ELSE N'' | |
| END | |
| + N';' | |
| END AS create_statement, | |
| partition_sums.row_count, | |
| si.is_primary_key, | |
| si.fill_factor, | |
| si.is_unique, | |
| si.auto_created AS is_auto_created, | |
| si.is_hypothetical, | |
| stat.user_seeks, | |
| stat.user_scans, | |
| stat.user_lookups, | |
| user_updates, | |
| (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, | |
| partition_sums.reserved_in_row_GB, | |
| partition_sums.reserved_LOB_GB, | |
| si.allow_page_locks, | |
| si.allow_row_locks, | |
| partition_sums.partition_count, | |
| 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 si | |
| JOIN sys.tables t ON si.object_id = t.object_id | |
| JOIN sys.schemas sc ON t.schema_id = sc.schema_id | |
| LEFT JOIN sys.dm_db_index_usage_stats 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 psc ON si.data_space_id = psc.data_space_id | |
| LEFT JOIN sys.partition_functions pf ON psc.function_id = pf.function_id | |
| LEFT JOIN sys.filegroups fg ON si.data_space_id = fg.data_space_id | |
| LEFT JOIN sys.xml_indexes xi ON xi.object_id = si.object_id and xi.index_id = si.index_id | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| i2.object_id, | |
| i2.index_id, | |
| i2.name as primary_name | |
| FROM sys.xml_indexes i2 | |
| ) sxi ON sxi.object_id = xi.object_id AND xi.using_xml_index_id = sxi.index_id | |
| /* Key list */ | |
| OUTER APPLY ( | |
| SELECT STRING_AGG(QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END, N', ') WITHIN GROUP (ORDER BY ic.key_ordinal) | |
| FROM sys.index_columns 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) | |
| ) AS keys ( key_column_list ) | |
| /* XML Key list */ | |
| OUTER APPLY ( | |
| SELECT STRING_AGG(QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END, N', ') WITHIN GROUP (ORDER BY ic.key_ordinal) | |
| FROM sys.index_columns 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 and is_included_column = 0) | |
| ) AS xmlkeys ( xmlkey_column_list ) | |
| /* Partitioning Column */ | |
| OUTER APPLY ( | |
| SELECT MAX(QUOTENAME(c.name)) AS column_name | |
| FROM sys.index_columns 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 STRING_AGG(QUOTENAME(c.name), N', ') WITHIN GROUP (ORDER BY c.name) | |
| FROM sys.index_columns 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 | |
| ) AS includes ( include_column_list ) | |
| /* 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 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 STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number) | |
| FROM sys.partitions p | |
| WHERE | |
| p.object_id = si.object_id | |
| and p.index_id = si.index_id | |
| and p.data_compression = 1 | |
| ) AS row_compression ( row_compression_partition_list ) | |
| /* page compression list by partition */ | |
| OUTER APPLY ( | |
| SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number) | |
| FROM sys.partitions p | |
| WHERE | |
| p.object_id = si.object_id | |
| and p.index_id = si.index_id | |
| and p.data_compression = 2 | |
| ) AS page_compression ( page_compression_partition_list ) | |
| /* columnstore compression list by partition */ | |
| OUTER APPLY ( | |
| SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number) | |
| FROM sys.partitions p | |
| WHERE | |
| p.object_id = si.object_id | |
| and p.index_id = si.index_id | |
| and p.data_compression = 3 | |
| ) AS columnstore_compression ( columnstore_compression_partition_list ) | |
| --/* XML compression list by partition: SQL Server 2022 onwards */ | |
| --OUTER APPLY ( | |
| -- SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number) | |
| -- FROM sys.partitions p | |
| -- WHERE | |
| -- p.object_id = si.object_id | |
| -- and p.index_id = si.index_id | |
| -- and p.xml_compression = 1 -- 2022 onwards | |
| -- ) AS xml_compression ( xml_compression_partition_list ) | |
| WHERE | |
| si.type IN (0, 1, 2, 3, 5, 6) | |
| ORDER BY | |
| sc.name, t.name, si.index_id | |
| OPTION(RECOMPILE); | |
| GO | |
by