Heaps_with_forwarded_records.sql

-- show fragmentation for heaps in database
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

IF OBJECT_ID('tempdb..#HeapDetails') IS NOT NULL
    DROP TABLE #HeapDetails;

CREATE TABLE #HeapDetails (
    ObjectName SYSNAME,
    PageCount INT,
    AvgPageSpaceUsedPercent FLOAT,
    RecordCount INT,
    ForwardedRecordCount INT
);

DECLARE HeapCursor CURSOR FOR
SELECT object_id
FROM sys.indexes
WHERE index_id = 0;

DECLARE @ObjectID INT;

OPEN HeapCursor;
FETCH NEXT FROM HeapCursor INTO @ObjectID;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #HeapDetails (ObjectName, PageCount, AvgPageSpaceUsedPercent, RecordCount, ForwardedRecordCount)
    SELECT 
        OBJECT_NAME(@ObjectID),
        page_count,
        avg_page_space_used_in_percent,
        record_count,
        forwarded_record_count
    FROM sys.dm_db_index_physical_stats (
        DB_ID(),
        @ObjectID,
        0,
        NULL,
        'DETAILED'
    );

    FETCH NEXT FROM HeapCursor INTO @ObjectID;
END

CLOSE HeapCursor;
DEALLOCATE HeapCursor;

SELECT *
FROM #HeapDetails
WHERE ForwardedRecordCount > 0
ORDER BY ObjectName;

DROP TABLE #HeapDetails;