Générer un script de Tous les index dans une base de données

C'est un excellent article sur SQL Server Centrale que j'ai utilisé.

Ici est un autre que vous aimeriez mieux.

Si ce n'est pas exactement ce que vous cherchez pour (tri, filtrage) je suis sûr que ce serait plus facile de modifier ces que de commencer à partir de zéro.

OriginalL'auteur Tim Lehner

TSQL De Script de Tous les Index d'une Base de données

Ce sera script:

1. Cluster et les index non-cluster (y compris index filtrés)
2. Le groupe de fichiers de l'index est créé sur (si non partitionné)
3. Le schéma de partition de l'index est créé sur (si partitionné)
4. Paramètres de Compression si l'index est comprimé et c'est par
   partition si la table est partitionnée
5. Pour les tables qui n'ont pas d'index cluster, ils seront toujours un
   ligne dans les résultats avec un commentaire indiquant qu'il est d'un tas de

#

    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 */
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO

OriginalL'auteur Kundan Dasange