DBCC REINDEX all user tables

• david wootton

  Old Hand

  Points: 338

  More actions

  October 6, 2007 at 4:04 am

  #179904

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

• tpelegrin

  Valued Member

  Points: 55

  More actions

  February 23, 2009 at 11:24 am

  #948788

  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

  More actions

  March 11, 2009 at 6:54 am

  #957428

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

• @STLSQLGuy

  Default port

  Points: 1476

  More actions

  November 2, 2010 at 2:30 pm

  #1245733

  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: 281334

  More actions

  November 2, 2010 at 2:33 pm

  #1245738

  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