Check status of HEAP tables

if exists (select 1 from information_schema.routines where routine_name = 'usp_heaptable_health')
    drop procedure usp_heaptable_health
go


create procedure usp_heaptable_health    @MinPages int = 2048
                                        , @Frag_percent numeric(5,2) = 25
                                        , @FRC_percent numeric(5,2) = .1
                                        , @GRC_percent numeric(5,2) = 1
                                        , @Tablepattern sysname = '%'
as
/*
    name: usp_heaptable_health
    author: Wilfred van Dijk (http://www.wilfredvandijk.nl, wvand@wilfredvandijk.nl)
    purpose: reports heap tables with potential problems

    parameters: @MinPages : minimum amount of datapages for a table
                @Frag_percent : minimum percentage of table fragmentation to report
                @FRC_percent: minimum percentage ForwardedRecord Count percentage to report
                @GRC_percent: minimum percentage GhostRecord Count percentage to report

    Although it's a general recommendation to have a clustered index on a table, not every 
    application is following this guidance. Since heap tables can't be defragged or can't 
    get rid of forwarded records, use this script to determine potential problems. I developed 
    this script after we had some serious problems with a heap table.

    Tables with a high fragmentation/ forwarded record count or ghost record count can be 
    fixed by either creating a clustered index or do the traditional export-truncate-import 
    (make sure you're aware of the consequences of these actions!)

    Note: By running this script your "page life expectancy" will drop 
    significantly (due to the dm_db_index_physical_stats usage)
*/    set nocount on

    declare @toggle bit
    declare @ObjectID int
    declare @Tablename sysname
    declare @ToProcess int

    set @toggle=0

    begin try
        drop table #db03632 
    end try
    begin catch
        -- dummy
    end catch
/*
    Cursor selects user tables with a minimum amount of DPages
*/    declare c_lus cursor local static for
        select id
        from sysindexes
        where dpages > @MinPages
        and indid = 0
        and id > 1000
        and object_name(id) like @Tablepattern

    open c_lus
    set @ToProcess = @@CURSOR_ROWS

    fetch next from c_lus into @ObjectID
    while @@fetch_status = 0
        begin
            set @ToProcess = @toProcess - 1
            set @tablename = object_name(@objectid)
            raiserror(N'Analyzing : %s, %i remaining ',10,1, @Tablename, @ToProcess)
            if @toggle = 0
                begin
                    SELECT    * 
                    into    #db03632 
                    FROM    sys.dm_db_index_physical_stats(DB_ID(), @ObjectId, NULL, NULL , 'DETAILED');
                    set @toggle = 1
                end
            else
                insert    into #db03632 
                select    * 
                FROM    sys.dm_db_index_physical_stats(DB_ID(), @ObjectId, NULL, NULL , 'DETAILED');

            fetch next from c_lus into @ObjectID
        end
    close c_lus
    deallocate c_lus
;
    with CTE(databasename, heaptable, page_count, fragment_count, avg_fragmentation_in_percent, record_count
            , ghost_record_count, ghost_record_count_in_percent, forwarded_record_count, forwarded_record_count_in_percent)
    as
    (
        select    db_name(database_id) as databasename 
                ,object_name(object_id) as heaptable
                , page_count
                , fragment_count
                , avg_fragmentation_in_percent
                , record_count
                , ghost_record_count
                , (cast(ghost_record_count as numeric(10,2))/ cast(coalesce(nullif(record_count,0),1) as numeric(10,2))) * 100 as ghost_record_count_in_percent
                , forwarded_record_count
                , (cast(forwarded_record_count as numeric(10,2)) / cast(coalesce(nullif(record_count,0),1) as numeric (10,2))) * 100 as forwarded_record_count_in_percent
        from    #db03632 
        where    index_type_desc = 'HEAP'
    )
    select    *
    from    CTE
    where    avg_fragmentation_in_percent > @frag_percent
            or forwarded_record_count_in_percent > @FRC_percent
            or ghost_record_count_in_percent > @GRC_percent
    order by page_count desc

    drop table #db03632
go