[SQL Server] Afficher les statistiques des index de toutes les bases de données
DROP TABLE IF EXISTS #IndexStatCurrent
DECLARE @sqlserver_start_time AS DATETIME
SELECT @sqlserver_start_time=sqlserver_start_time FROM sys.dm_os_sys_info
CREATE TABLE #IndexStatCurrent(
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [sysname] NOT NULL,
[TableName] [sysname] NOT NULL,
[IndexName] [sysname] NULL,
[IndexType] [nvarchar](60) NULL,
[user_seeks] [bigint] NOT NULL,
[user_scans] [bigint] NOT NULL,
[user_lookups] [bigint] NOT NULL,
[user_updates] [bigint] NOT NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL
)
EXEC sp_MSforeachdb '
USE [?]
IF DB_NAME() LIKE ''%MEKTABA%''
BEGIN
INSERT INTO #IndexStatCurrent
([DatabaseName],[SchemaName],[TableName],[IndexName],[IndexType]
,[user_seeks],[user_scans],[user_lookups],[user_updates],[last_user_seek]
,[last_user_scan],[last_user_lookup],[last_user_update])
SELECT
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
us.user_seeks ,
us.user_scans ,
us.user_lookups ,
us.user_updates ,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
FROM sys.dm_db_index_usage_stats AS us
JOIN sys.indexes AS i ON i.object_id = us.object_id
AND i.index_id = us.index_id
JOIN sys.tables AS t ON t.object_id = us.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE us.database_id = DB_ID() -- base courante
AND i.index_id > 0 -- exclut le “heap” (index_id = 0)
ORDER BY SchemaName, TableName, IndexName
END
'
SELECT @sqlserver_start_time AS sqlserver_start_time,* FROM #IndexStatCurrent
DROP TABLE IF EXISTS #IndexStatCurrent