DBCC DBREINDEX

• shanila_minnu

  Hall of Fame

  Points: 3465

  More actions

  July 11, 2011 at 7:46 am

  #241324

  DECLARE @TableName sysname

  DECLARE cur_reindex CURSOR FOR

  SELECT table_name

  FROM information_schema.tables

  WHERE table_type = 'base table'

  OPEN cur_reindex

  FETCH NEXT FROM cur_reindex INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  PRINT 'Reindexing ' + @TableName + ' table'

  DBCC DBREINDEX (@TableName, ' ', 90)

  FETCH NEXT FROM cur_reindex INTO @TableName

  END

  CLOSE cur_reindex

  DEALLOCATE cur_reindex

  above query is throwing error like "Could not find a table or object named 'Client_102680ENG9886'. Check sysobjects."

  Reason for this is there are tables with owner apart from 'dbo'.

  Kindly give me query to reindex all the tables in database which is having owner(schema) apart from dbo

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 11, 2011 at 8:36 am

  #1351112

  Have a look through the Scripts section of this web site. Here's one that I found on a quick search: http://www.sqlservercentral.com/scripts/Administration/72594/

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 11, 2011 at 9:27 am

  #1351160

  I recommend using either Ola Hallengren's maintenance script for re-indexing or Michelle Ufford's script[/url]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question