I had a request regarding how to identify unused indexes across all databases on a SQL Server instance (rather than just the current one). I’ve written this script before so thought I’d post it up here on my blog for the future use of myself and others.

The script uses the undocumented sp_MSforeachdb internal stored procedure to iterate through all the databases (excluding the system ones) and store the results of a standard unused indexes script into a temp table. The table can then be queried to analyse the results.

This script comes with a caveat, that the sp_MSforeachdb procedure can be unreliable and on occasion skip databases so you should check that the results include all the databases you are interested in. A simple count of distinct database names, and comparing that against the number of databases on your instance should be sufficient.

If you are trying to analyse in the case where you have multiple instances of the same application database, e.g. one for each client, you can query the temp table to identify indexes that are unused for all clients.

Anyway here’s the script:

--Find unused indexes across all user databases
IF OBJECT_ID('tempdb..#UnusedIndexes') IS NOT NULL
DROP TABLE #UnusedIndexes;
SELECT TOP 0
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
o.name AS TableName,
i.name AS IndexName,
iu.user_updates as IndexUpdates,
iu.user_lookups as UserLookups,
iu.user_seeks AS UserSeeks,
iu.user_scans as UserScans
INTO #UnusedIndexes
FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.objects o
ON iu.object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
AND i.index_id = iu.index_id
WHERE i.is_primary_key = 0
AND i.is_unique = 0
AND iu.user_lookups = 0
AND iu.user_scans = 0
AND iu.user_seeks = 0;
EXEC sp_MSforeachdb '
USE [?]
IF DB_ID() > 4
BEGIN
INSERT INTO #UnusedIndexes
SELECT
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
o.name AS TableName,
i.name AS IndexName,
iu.user_updates as IndexUpdates,
iu.user_lookups as UserLookups,
iu.user_seeks AS UserSeeks,
iu.user_scans as UserScans
FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.objects o
ON iu.object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
AND i.index_id = iu.index_id
WHERE i.is_primary_key = 0
AND i.is_unique = 0
AND iu.user_lookups = 0
AND iu.user_scans = 0
AND iu.user_seeks = 0;
END'
SELECT * FROM #UnusedIndexes;
--DROP #UnusedIndexes;

Note: Partially in response to Jeff’s comment below. Proceed with caution before dropping any indexes the script identifies. Stats are only kelp since the last restart, so if that was recent then you may have required indexes that get listed. Also think about whether you might have any occasional processes, such as month, quarter, or year-end processing that will not have executed since that last restart and may require indexes.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.