Abhijit, your solution gives me only unused indexes in the current database. I'm looking for a solution to find all unused indexes in a number of databases, which have all the same structure, but are named differently.

Chirag, your solution gives me errors at the Inner join

DECLARE @Database TABLE(Naam VarChar(20))

DECLARE @IndexNaam TABLE(

DatabaseName VarChar(Max)

,Database_IdInt

, TableName VarChar(Max)

, IndexName VarChar(Max)

, IndexId Int

)

DECLARE @TableCount Int

DECLARE @SqlCmd nVarChar(Max)

INSERT @DataBase(Naam)

SELECT name FROM sys.databases D

WHERE D.name like 'McBam%'

SELECT * FROM @DataBase

SELECT @TableCount = (SELECT COUNT(*) FROM @DataBase)

Print @TableCount

SET @SqlCmd = 'USE ?;SELECT DB_NAME() AS DATABASENAME,

OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,

B.NAME AS INDEXNAME,

B.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS C

WHERE B.OBJECT_ID = C.OBJECT_ID

AND B.INDEX_ID = C.INDEX_ID

INNER JOIN @Database D ON C.database_id = D.database_id

WHERE NOT EXISTS (SELECT *

FROM SYS.DM_DB_INDEX_USAGE_STATS C

WHERE B.OBJECT_ID = C.OBJECT_ID

AND B.INDEX_ID = C.INDEX_ID)

AND A.TYPE <> '''+'S'+'''

AND B.NAME > ''''

AND B.NAME not like '''+'%rowid%'+'''

ORDER BY 1, 2, 3 '

INSERT @IndexNaam (DatabaseName, TableName, IndexName, IndexId)

EXEC sp_MSforeachdb @SqlCmd

SELECT TableName, IndexName, Count(*) as Aantal FROM @DataBase

Inner Join @IndexNaam on DatabaseName = Naam

Group by TableName, IndexName

Having Count(*) = @TableCount

Order By TableName, IndexName

So, I guess it boils down to: How do I restrict the sp_MSforeachdb to only the databases I want?

Cees