What are forwarded records?
Forwarded records are records that reside on a new page when a heap table (table without clustered index) is updated, and the rows are no longer able to stay on the same page,
which causes a forwarding pointer to point to the new page that the row resides in.
Many forwarded records can cause serious I/O issues, and if this happens a lot, it is advised to create a clustered index on the table.
You can also rebuild the table to remove the forwarded records (high overhead).
sys.dm_db_index_physical_stats is the dmv used to find the forwarded records,
but this is a very I/O intensive dmv to query, which is why we will apply filtering in the script.
We will use a temp table to apply filtering, and only include the heap tables in our dmv checks.
SELECT O.* INTO #temp FROM sys.objects O inner join sys.tables AS T on o.object_id = T.object_id INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id INNER JOIN sys.indexes AS I ON T.object_id = I.object_id AND I.type = 0; SELECT DB_NAME(db_id()) as DatabaseName, t.name as TableName, index_id, index_type_desc, avg_fragmentation_in_percent, page_count, record_count, forwarded_record_count FROM #temp t CROSS APPLY sys.dm_db_index_physical_stats ( DB_ID() ,t.object_id ,NULL ,NULL ,'DETAILED' ) WHERE forwarded_record_count > 0 and t.is_ms_shipped =0 DROP TABLE #temp
I hope this script helped you achieve your goal, with the least amount of overhead.