Returns which indexes should be rebuilt and which can be reorganized to improve index performance

IndexFragmentationQuery.sql

--  General Maintenance Notes on Indexes
--  Indexes should be rebuilt when index fragmentation is greater than 30%
--	Indexes should be reorganized when index fragmentation is between 5% and 30%
--	Index rebuilding is much more costly than reorganization.

-- Rebuild
DECLARE @database_id INT
SET @database_id = DB_ID();
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats (@database_id, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = @database_id AND name IS NOT NULL AND avg_fragmentation_in_percent > 30
ORDER BY ps.avg_fragmentation_in_percent DESC
GO

-- Reorganize
DECLARE @database_id INT
SET @database_id = DB_ID();
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats (@database_id, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = @database_id AND name IS NOT NULL AND avg_fragmentation_in_percent > 5 AND avg_fragmentation_in_percent <= 30
ORDER BY ps.avg_fragmentation_in_percent DESC
GO