[SQL Server] Afficher la liste des index pour toutes les bases de données
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 07/07/2022
-- Description: Afficher les Index de toutes les bases
-- =============================================
IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#IndexBases]'))
BEGIN
DROPTABLE #IndexBases
END
CREATETABLE #IndexBases
(
[NomBase] [nvarchar](128)NULL,
[NomTable] [nvarchar](128)NULL,
[object_id] [int] NOTNULL,
[name] [sysname] NULL,
[index_id] [int] NOTNULL,
[type] [tinyint] NOTNULL,
[type_desc] [nvarchar](60)NULL,
[is_unique] [bit] NULL,
[data_space_id] [int] NULL,
[ignore_dup_key] [bit] NULL,
[is_primary_key] [bit] NULL,
[is_unique_constraint] [bit] NULL,
[fill_factor] [tinyint] NOTNULL,
[is_padded] [bit] NULL,
[is_disabled] [bit] NULL,
[is_hypothetical] [bit] NULL,
[allow_row_locks] [bit] NULL,
[allow_page_locks] [bit] NULL,
[has_filter] [bit] NULL,
[filter_definition] [nvarchar](max)NULL
)
executesp_MSforeachdb'
if UPPER(''?'') not in (''MASTER'',''TEMPDB'',''MODEL'',''MSDB'',''DISTRIBUTION'')
begin
use [?];
INSERT INTO #IndexBases
([NomBase],[NomTable],[object_id],[name],[index_id],[type]
,[type_desc],[is_unique],[data_space_id],[ignore_dup_key]
,[is_primary_key],[is_unique_constraint],[fill_factor]
,[is_padded],[is_disabled],[is_hypothetical]
,[allow_row_locks],[allow_page_locks],[has_filter],[filter_definition])
select DB_NAME() AS NomBase,OBJECT_NAME(object_id) AS NomTable,*
from sys.indexes
end'
SELECT*FROM #IndexBases ORDERBY [NomBase],[NomTable],[name]
DROPTABLE #IndexBases