Today I had to quickly find which indexes are not used on a database and the size of each one. Because of the hurry, I just googled a little and found two scripts with parts of the solution. I simply joined both scripts in a single one, and here you have it.
This script will give you the size of the unused indexes larger than 0 pages and not starting with he “PK” string (we can’t drop Primary Keys to gain space, even when not in use). Please don’t mind the bad spacing. Links to original scripts in the commentary.
/* Unused Indexes Size https://thelonelydba.wordpress.com based on scripts from: http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server */ DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID, (s.used_page_count) * 8 AS IndexSizeKB FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID join sys.dm_db_partition_stats AS s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN (SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbid) and left(i.name,2) <> 'PK' and s.used_page_count <> 0 ORDER BY s.used_page_count desc, OBJECTNAME, I.INDEX_ID, INDEXNAME GO