sql_index_rebuild.sql

-- The following statements are from multiple sources
-- I'm not exactly sure where each one is coming from
-- Created it a few years ago, and refound it deep on my hd
-- Web sources must be: 
-- blog.sqlauthority.com, www.brentozar.com
-- And books like:
-- "SQL Performance Explained", "SQL Server Query Performance Tuning Distilled"
-- "Professional SQL Server 2008 Internals and Troubleshooting", "SQL Tuning", etc.

with fragments as (SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as Tablename, 
dbindexes.[name] as Indexname,
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)

select * from (
select 'ALTER INDEX ' + Indexname + ' ON ' + tablename + ' REBUILD;' as s, avg_fragmentation_in_percent 
from fragments where avg_fragmentation_in_percent >= 40
union
select 'ALTER INDEX ' + Indexname + ' ON ' + tablename + ' REORGANIZE;' as s, avg_fragmentation_in_percent  
from fragments where avg_fragmentation_in_percent < 40  and avg_fragmentation_in_percent > 10
) g order by 2 desc