Simple script to ReIndex complete database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by $sanjayattray » Simple script to ReIndex complete database

Simple script to ReIndex complete database

Rate Topic

Display Mode

Topic Options

Author

Message

SanjayAttray

Posted Sunday, September 23, 2007 4:48 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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

SQL DBA.

Post #401734

jmartin-697613

Posted Wednesday, April 02, 2008 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2008 12:45 PM
Points: 1, Visits: 1

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

Post #478689

« Prev Topic | Next Topic »

Permissions