Simple script to ReIndex complete database

• SanjayAttray

  SSChampion

  Points: 13157

  More actions

  September 23, 2007 at 4:48 pm

  #181123

  Comments posted to this topic are about the item Simple script to ReIndex complete database

  SQL DBA.

• jmartin-697613

  SSC Rookie

  Points: 25

  More actions

  April 2, 2008 at 10:39 am

  #797390

  This is the same script without the overhead of the cursor and taking into account that views are also in the information_schema.tables view (and should not be returned byt the query:

  DECLARE @TableName varchar(255)

  SET @TableName = (SELECT TOP 1 TABLE_NAME FROM information_schema.tables WHERE table_name not like 'sys%' and table_name <>'dtproperties' and TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME asc)

  WHILE @TableName is not null

  BEGIN

  PRINT 'Reindexing ' + @TableName

  BEGIN TRY

  DBCC DBREINDEX(@TableName,' ',0)-- change fill factor here as per your requirement

  END TRY

  BEGIN CATCH

  PRINT 'Error reindexing ' + @TableName

  END CATCH

  SET @TableName = (SELECT TOP 1 TABLE_NAME FROM information_schema.tables WHERE table_name not like 'sys%' and table_name <>'dtproperties' and TABLE_TYPE = 'BASE TABLE' and cast(table_name as varchar(255)) > @TableName order by TABLE_NAME asc)

  END