Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple script to ReIndex complete database Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 4:48 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
Comments posted to this topic are about the item Simple script to ReIndex complete database

SQL DBA.
Post #401734
Posted Wednesday, April 02, 2008 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse