[SQL Server] Afficher les Index fragmentés sur de toutes les bases de données
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 07/07/2022
-- Description: Afficher les Index fragmentés sur de toutes les bases de données
-- =============================================
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#IndexesFragmentes]'))
BEGIN
DROPTABLE #IndexesFragmentes
END
CREATETABLE #IndexesFragmentes
(
[database_name] [nvarchar](128)NULL,
[table_name] [nvarchar](128)NULL,
[index_name] [sysname] NULL,
[index_type] [nvarchar](60)NULL,
[avg_fragmentation_in_percent] [float] NULL,
[page_count] [bigint] NULL,
[fragment_count] [bigint] NULL,
[avg_fragment_size_in_pages] [float] NULL
)
executesp_MSforeachdb'
if UPPER(''?'') not in (''MASTER'',''TEMPDB'',''MODEL'',''MSDB'',''DISTRIBUTION'')
begin
use [?];
INSERT INTO #IndexesFragmentes
([database_name],[table_name],[index_name],[index_type]
,[avg_fragmentation_in_percent],[page_count],[fragment_count],[avg_fragment_size_in_pages])
SELECT DB_NAME(T1.database_id) AS database_name ,
OBJECT_NAME(T1.object_id) AS table_name ,
T2.name AS index_name ,
T1.index_type_desc AS index_type ,
T1.avg_fragmentation_in_percent ,
T1.page_count ,
T1.fragment_count ,
T1.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(N''?''), NULL, NULL, NULL, ''DETAILED'') T1
INNER JOIN sysindexes T2 ON T1.object_id = T2.id AND T1.index_id = T2.indid
WHERE T1.avg_fragmentation_in_percent >= 45
ORDER BY T1.avg_fragmentation_in_percent DESC
end'
SELECT*FROM #IndexesFragmentes ORDERBY [database_name],table_name,index_name
DROPTABLE #IndexesFragmentes