• Since the script is based on sys.dm_db_index_usage_stats, it only returns unused indexes that exist in sys.dm_db_index_usage_stats. Other unused indexes can be found by a different query with "WHERE NOT EXISTS (select ... from sys.dm_db_index_usage_stats ). Something like this:

    SELECT DB_NAME() AS DATABASENAME,

    --OBJECT_NAME(B.OBJECT_ID) AS 'Table',

    obj.name as 'Table',

    B.NAME AS 'Index',

    idx.rowcnt, idx.used/128 'MB', substring(b.type_desc,1,20), 'No Stats', is_unique as 'unique', is_primary_key as 'primary'

    FROM SYS.OBJECTS obj

    INNER JOIN SYS.INDEXES B ON obj.OBJECT_ID = B.OBJECT_ID

    join SYS.SYSINDEXES idx ON B.OBJECT_ID = idx.ID and B.index_id = idx.indid

    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 obj.TYPE 'S'

    and B.NAME is not NULL

    and b.is_unique = 0 and b.is_primary_key = 0 -- don't include unique or primary keys

    ORDER BY obj.name , idx.NAME