Index defragmentation query

I'm not sure where I got this, but I've had several cases in which I had to defragment the indexes in a database. This script goes through the indexes in the database, shows the fragmentation, and also gives you a column with the actual defragmentation query. You can highlight these commands and run them in a new query to do all the defragmentation. 


SELECT as 'Schema', as 'Table', as 'Index',
'ALTER INDEX [' + + '] ON [' + + '] REBUILD' AS Command
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc


Tag(s): SQL Server

An error has occurred. This application may no longer respond until reloaded. Reload 🗙