Post reply

Simple script to ReIndex complete database

  • 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.

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply