-- Produces report of index fragmentation

-- For highly-fragmented indexes, use the table name in column 2 and produce the following
-- SQL commands:
--
-- ALTER INDEX ALL ON MyTable REBUILD;
--
-- To generate the commands in Excel:
--
-- 1. Copy the second column (with header) to a spreadsheet
-- 2. Remove the duplicate rows (the report lists the tables by index and some tables 
--    have more than one index, while the command rebuilds all indexes on the table).
-- 3. Copy the following formula to the second column in the first table row (row 2):
--
-- =CONCATENATE("ALTER INDEX ALL ON ", A2, " REBUILD")
-- 
-- 4. Copy/paste the formula to every row.
-- 5. Copy the resulting unique values (i.e., remove the duplicates) back to SQL Server Management Studio
-- 6. Execute each copied command in SQL Server Management Studio.
--
-- To determine how frequently indexes should be rebuilt will take re-running this script
-- immediately after rebuilding the indexes to obtain a baseline report and then at
-- periodic intervals (e.g. daily or weekly) and compare the new report to the baseline.
-- The tables with a large page count and high increase in fragmentation would indicate
-- a need to rebuild either on the measurement interval or more frequently.  Note:  Some
-- of the tables are transient in nature, meaning rebuilding the index frequently may
-- not have a large positive impact on performance.  The likely candidates would be table
-- that include "TMP" or "BATCH" in the name.  Refer to the EnterWorks Maintenance guide
-- for more information.
--


SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and dbindexes.[name] is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc