Pipboy level utility!
I found a really cool example for getting all the fragmentation on a database. The example was so cool that I decided to adapt my previous script to this one and made a couple adjustments.
This new revision of the script will output the TSQL that you can use to either Rebuild, Reorganize the index or in the case of heaps, rebuild the table. The scripts that are generated are based on > 30% fragmentation, 5% fragmentation
UPDATE: 02122014 – I just discovered a bug (or error) in this script. The script is not accounting for heaps. I found this out while I was performing some load testing experiments (yay for testing). I have changed the output of the rebuild statement while I debug
UPDATE: 09032014 – OK, so I think I got this bad boy working. I ended up adding a check for when the table is a heap by the sys.indexes.type_desc = ‘HEAP’
SELECT DISTINCT
sc.name AS [Schema Name] ,
DB_NAME(ps.database_id) AS [Database Name] ,
OBJECT_NAME(ps.OBJECT_ID) AS [Table Name] ,
ISNULL(ix.name, 'HEAP') AS [Index Name] ,
ps.avg_fragmentation_in_percent AS [Frag %] ,
ps.page_count AS [Page Count] ,
ix.is_disabled AS [Disabled] ,
ix.is_hypothetical AS [Hypothetical] ,
ix.fill_factor ,
CASE WHEN ps.avg_fragmentation_in_percent > 30
AND ix.TYPE 0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id)
+ '.' + sc.name + '.' + OBJECT_NAME(ps.OBJECT_ID)
+ ' REBUILD'
WHEN ps.avg_fragmentation_in_percent < 30
AND ix.TYPE 0
AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id)
+ '.' + sc.name + '.' + OBJECT_NAME(ps.OBJECT_ID)
+ ' REORGANIZE'
WHEN ix.type_desc = 'HEAP'
THEN 'ALTER TABLE ' + DB_NAME(ps.database_id) + '.' + sc.name
+ '.' + OBJECT_NAME(ps.OBJECT_ID) + ' REBUILD'
ELSE 'If you have reached this message, please email dbassassin@codepimp.org with as much information as possible'
END AS [Fix TSQL]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.tables tb ON tb.OBJECT_ID = ps.OBJECT_ID
INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id
INNER JOIN sys.indexes ix ON ps.OBJECT_ID = ix.OBJECT_ID
AND ps.index_id = ix.index_id
WHERE ps.database_id = DB_ID()
AND avg_fragmentation_in_percent > 5
ORDER BY page_count DESC
--OPTION (MAXDOP 1)