How much space are you wasting?

The Below script would provide us​ the space used by write only indexes.
The usage stats from the indexes is pretty easy to get from ‘sys.dm_db_index_usage_stats’
and if you join the DMV ‘sys.dm_db_partition_stats’
onto that, you can from the column ‘used_page_count’ calculate the number of
bytes that the particular indexes is wasting.

Here is the script, that I ended up with:

WITH WastedMB
AS
(
SELECT
t1.database_id,
t1.object_id,
t1.index_id,
t2.name,
t3.partition_number,
t3.used_page_count
FROM
sys.dm_db_index_usage_stats t1
INNER JOIN sys.indexes t2 ON (t1.object_id =
t2.object_id) AND (t1.index_id = t2.index_id)
INNER JOIN
sys.dm_db_partition_stats t3 ON (t1.object_id = t3.object_id) AND (t1.index_id =
t3.index_id)
WHERE
    database_id = DB_ID() and
    user_seeks = 0 and
user_scans = 0 and user_lookups = 0 and
   
OBJECTPROPERTY(t1.[object_id],'IsUserTable') = 1 and t2.index_id > 1
and
    t2.is_unique = 0 and t2.is_unique_constraint =
0
)
SELECT
database_id,
object_id,
index_id,
name,
COUNT(*)
AS numofpartitions,
SUM(used_page_count) AS
UsedPages,
(SUM(used_page_count) * 8) / 1024 AS spacewasteinmb
FROM
WastedMB
GROUP BY database_id, object_id, index_id,name
ORDER BY
1,2,3
COMPUTE SUM((SUM(used_page_count) * 8) / 1024)

As always be careful when you drop indexes – better check an extra time
before dropping