Re-indexing tables

Question

Post reply

Re-indexing tables

Peter Gadsby

SSCrazy

Points: 2718
More actions
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

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

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

Using sp_msforeachdb?

Using sp_msforeachtable?

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

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.