[SQL Server] Trouver les Index Non Utilisées sur toutes les bases de données
IF OBJECT_ID('dbo.IndexUnusedHistory') IS NOT NULL
DROP TABLE dbo.IndexUnusedHistory;
GO
CREATE TABLE dbo.IndexUnusedHistory
(
CaptureDate DATETIME NOT NULL DEFAULT GETDATE(),
DatabaseName SYSNAME,
SchemaName SYSNAME,
TableName SYSNAME,
IndexName SYSNAME,
IndexType NVARCHAR(60),
user_seeks BIGINT,
user_scans BIGINT,
user_lookups BIGINT,
user_updates BIGINT
);
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
USE [' + name + '];
-- Index classiques
INSERT INTO DBA_Tools.dbo.IndexUnusedHistory
(
DatabaseName, SchemaName, TableName, IndexName, IndexType,
user_seeks, user_scans, user_lookups, user_updates
)
SELECT
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
ISNULL(us.user_seeks, 0) AS user_seeks,
ISNULL(us.user_scans, 0) AS user_scans,
ISNULL(us.user_lookups, 0) AS user_lookups,
ISNULL(us.user_updates, 0) AS user_updates
FROM sys.indexes i
INNER JOIN sys.objects t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON i.object_id = us.object_id
AND i.index_id = us.index_id
AND us.database_id = DB_ID()
WHERE t.is_ms_shipped = 0
AND i.type_desc <> ''HEAP''
AND ISNULL(us.user_seeks, 0) = 0
AND ISNULL(us.user_scans, 0) = 0
AND ISNULL(us.user_lookups, 0) = 0;'
FROM sys.databases
WHERE database_id > 4 -- exclut master, model, msdb, tempdb
AND state_desc = 'ONLINE';
EXEC sp_executesql @sql;