Post reply

Re-indexing tables

  • November 28, 2006 at 7:23 am

    #61952

    Hi,

    I have written a stored procedure that reads through all the tables in a database and performs a DBCC DBREINDEX on each of the tables. But  I would like to parameterise the database to reindex, any ideas how I could do this, as INFORMATION_SCHEMA is associated with the current database.

    SP as follows:-

    CREATE PROCEDURE DATABASE_REINDEX

     (

     @JOB_NAME     varchar(255)   = 'DATABASE_REINDEX',

     @PACKAGE_NAME   varchar(255)   = 'Stand Alone',

     @STEP_SEQUENCE_NO  int      =  1

    &nbsp

    AS

    DECLARE

     @TABLE_NAME    varchar(255),

     @SQL_COMMAND    varchar(8000),

     @STORED_PROCEDURE_NAME  varchar(255),

     @SEQUENCE_NO    int,

     @MESSAGE     varchar(8000),

     @ERROR_NO     int,

     @ROWCOUNT     bigint

     

    SET @STORED_PROCEDURE_NAME = 'DATABASE_REINDEX'

    SET @MESSAGE = 'Start of Procedure ' + @STORED_PROCEDURE_NAME

    SET @SEQUENCE_NO = 1

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

    SET @MESSAGE = 'Declaring Table Cursor'

    SET @SEQUENCE_NO = 2

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

    DECLARE TABLE_CURSOR CURSOR FOR

    SELECT

     TABLE_NAME

    FROM

     INFORMATION_SCHEMA.TABLES

    WHERE

     TABLE_TYPE = 'BASE TABLE'

    SET @MESSAGE = 'Opening Table cursor'

    SET @SEQUENCE_NO = 3

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

    OPEN TABLE_CURSOR

    SET @MESSAGE = 'Fetching first row'

    SET @SEQUENCE_NO = 4

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

    FETCH NEXT FROM TABLE_CURSOR into @TABLE_NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

     SET @MESSAGE = 'Reindexing - ' + @TABLE_NAME

     

     SET @SEQUENCE_NO = 5

     exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

     SET @SQL_COMMAND = 'dbcc dbreindex (' +  ''''  + @TABLE_NAME + ''')'

     EXEC (@SQL_COMMAND)

     FETCH NEXT FROM TABLE_CURSOR into @TABLE_NAME

    END

    SET @MESSAGE = 'Closing table cursor'

    SET @SEQUENCE_NO = 6

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

     

    CLOSE TABLE_CURSOR

    DEALLOCATE TABLE_CURSOR

    SET @MESSAGE = 'End of Procedure ' + @STORED_PROCEDURE_NAME

    SET @SEQUENCE_NO = 99

    exec INS_DEBUG_LOG @JOB_NAME,@PACKAGE_NAME,@STORED_PROCEDURE_NAME,@STEP_SEQUENCE_NO,@SEQUENCE_NO,@MESSAGE

     

    --database_reindex

    GO

     

    Thanks

     

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 28, 2006 at 9:50 am

    #674443

    Using cursor (master..sysdatabases, xx..sysobjects)?

    Using sp_msforeachdb?

    Using sp_msforeachtable?

  • November 28, 2006 at 11:10 am

    #674464

    The easiest way is the advice above using the sp_ms procedures. These aren't supported, but they will likely be continued since some MS procs use them.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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