DECLARE @MyTableVar TABLE ([schema_name] nvarchar(256),[object_name] nvarchar(256),index_name nvarchar(256),index_type nvarchar(120), avg_fragmentation_in_percent float(8),page_count bigint,alloc_unit_type_desc nvarchar(120));
DECLARE @Reg VARCHAR(255);
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT [name] FROM sys.tables;
OPEN MY_CURSOR;
FETCH NEXT FROM MY_CURSOR INTO @Reg;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @MyTableVar SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name, OBJECT_NAME(ips.object_id) AS object_name, i.name AS index_name, i.type_desc AS index_type, ips.avg_fragmentation_in_percent, ips.page_count, ips.alloc_unit_type_desc FROM sys.dm_db_index_physical_stats(DB_ID(), (SELECT st.object_id FROM SYS.tables st WHERE st.[name] = @Reg), default, default, 'LIMITED') AS ips INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id ORDER BY page_count DESC;
FETCH NEXT FROM MY_CURSOR INTO @Reg;
END
CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;
SELECT * FROM @MyTableVar WHERE index_type NOT IN ('HEAP') AND object_name NOT IN ('sysdiagrams') AND avg_fragmentation_in_percent > 0 ORDER BY 2;
GO