Fill Factor and wasted space: Quick Tip

This is a little excessive, but here's a quick script to show you your fill factors per table that are not at 100 as well as how much unused space you have per table. It also creates a script to rebuild, be aware of the options that are set.

select db.name as databasename, tbl.name as tablename, idx.name as indexname,
stats.index_id, fill_factor, last_user_update,user_seeks, user_scans,
user_lookups, user_updates, part.rows, part.data_compression_desc,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
'ALTER INDEX [' + idx.name + '] ON [' + Schema_Name(sch.schema_id) +
'].[' + object_Name(idx.object_id) +
'] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 100, PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS
= ON, ONLINE = ON, SORT_IN_TEMPDB = ON )' as Rebuild_Script
from sys.dm_db_index_usage_stats stats
inner join sys.tables as tbl on stats.object_id = tbl.object_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on stats.database_id = db.database_id
inner join sys.indexes as idx on stats.object_id = idx.object_id
and stats.index_id = idx.index_id
inner join sys.partitions as part on stats.object_id = part.object_id
and stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
where fill_factor != 0
and
fill_factor != 100
group by user_updates, fill_factor, db.name, tbl.name, idx.name, stats.index_id, fill_factor, last_user_update,user_seeks, user_scans,
user_lookups, user_updates, part.rows, part.data_compression_desc, sch.schema_id, idx.object_id