SQL Analyze Index fragmentation

SqlFragmentationPerIndex.sql

WITH fragmented_indexes AS
(
	SELECT 
		s.name [schema], t.name [table], i.name [index], ips.page_count, 
		ips.avg_fragmentation_in_percent, 
		ips.page_count * ips.avg_fragmentation_in_percent weight
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ips
		JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
		JOIN sys.tables t ON i.object_id = t.object_id
		JOIN sys.schemas s ON t.schema_id = s.schema_id
	WHERE page_count >= 1000 and avg_fragmentation_in_percent >= 5
)
SELECT * 
FROM fragmented_indexes
ORDER BY weight DESC;

SqlFragmentationPerTable.sql

WITH fragmented_indexes AS
(
	SELECT 
		s.name [schema], t.name [table], i.name [index], ips.page_count, 
		ips.avg_fragmentation_in_percent, 
		ips.page_count * ips.avg_fragmentation_in_percent weight
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ips
		JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
		JOIN sys.tables t ON i.object_id = t.object_id
		JOIN sys.schemas s ON t.schema_id = s.schema_id
	WHERE page_count >= 1000 and avg_fragmentation_in_percent >= 5
)
SELECT 
	[schema], [table], COUNT(1) indexes, SUM(page_count) page_count,  
	AVG(avg_fragmentation_in_percent) avg_fragmentation_in_percent, 
	SUM(weight) weight
FROM fragmented_indexes
GROUP BY [schema], [table]
ORDER BY weight DESC;