DBCC REINDEX all user tables

  • david wootton

    Old Hand

    Points: 328

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

  • tpelegrin

    Valued Member

    Points: 55

    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.

    :Whistling:

  • davidw

    SSC Enthusiast

    Points: 128

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

  • @STLSQLGuy

    Default port

    Points: 1476

    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,

    sysindexes.origfillfactor,

    #tmp.sch

    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)

    BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN

    print ' '

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

    print @msg

    exec (@msg)

    print ' '

    END

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

    END

    CLOSE reindexcursor

    DEALLOCATE reindexcursor

    GO


    thanks, ERH
  • SQLRNNR

    SSC Guru

    Points: 281210

    Nice change and thanks for sharing.

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

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

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