DBCC REINDEX all user tables

  • Comments posted to this topic are about the item DBCC REINDEX all user tables

  • Hi, I have recently try to execute it using SQL Server 2000 but with no success, the erros message is :

    Serveur : Msg 207, Niveau 16, État 3, Ligne 24

    'origfillfactor' : nom de colonne incorrect.

    Serveur : Msg 207, Niveau 16, État 1, Ligne 24

    'origfillfactor' : nom de colonne incorrect.


  • Check to make sure you have delared the variable it is complaing about

  • This is still 95% his code but I modified it a little to work with databases that have schemas other than DBO. It also uses the newer 2005 tables instead of SYSOBJECTS. I needed to use this to work with a JD Edwards implementation. Thanks Mr. Wootton.


    drop table #tmp

    declare @msg varchar(500)

    select x.name,

    x.object_id AS id,

    y.name AS sch

    into #tmp

    from sys.tables x, sys.schemas y

    where type = 'U'

    and y.schema_id = x.schema_id

    and x.name LIKE 'F%' -----you can omit this line, all my real tables started with F

    declare reindexcursor cursor for

    select #tmp.name,



    from sysindexes, #tmp

    where sysindexes.id = (select #tmp.id

    from #tmp

    where #tmp.id=sysindexes.id)

    group by #tmp.name,sysindexes.origfillfactor,#tmp.sch

    having count(#tmp.name) >= 1

    order by 1

    open reindexcursor

    declare @tname varchar(100)

    declare @ffact varchar(10)

    declare @tsch varchar(10)

    fetch next from reindexcursor into @tname,@ffact,@tsch

    WHILE (@@FETCH_STATUS <> -1)


    IF (@@FETCH_STATUS <> -2)


    print ' '

    SELECT @msg = 'DBCC DBREINDEX ('''+@tsch+'.'+@tname+''', '' '','+(@ffact)+')'

    print @msg

    exec (@msg)

    print ' '


    FETCH NEXT FROM reindexcursor INTO @tname, @ffact,@tsch


    CLOSE reindexcursor

    DEALLOCATE reindexcursor


    thanks, ERH
  • Nice change and thanks for sharing.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply