Scribd
Upload
3 views1 page

Reindex All Database Tables Script

This code snippet uses a cursor to loop through all base tables in a database and reindex each table using the DBCC DBREINDEX command. It declares variables to store the table name from the cursor and database name. The cursor fetches each table name and the DBREINDEX command reindexes it, printing the table name, with 90% maximum pages for parallelism.

Uploaded by

Nghia Tran Trong
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views1 page

Reindex All Database Tables Script

This code snippet uses a cursor to loop through all base tables in a database and reindex each table using the DBCC DBREINDEX command. It declares variables to store the table name from the cursor and database name. The cursor fetches each table name and the DBREINDEX command reindexes it, printing the table name, with 90% maximum pages for parallelism.

Uploaded by

Nghia Tran Trong
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Following is code snippet for reindex all tables in a database

USE your database name

DECLARE @TableName varchar(255)


DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Note:

     -  This code based on dbreindex command - reindex a table - and cursor to browse all tables in
database.

     - Reference :

                      + dbreindex  command: [Link]

You might also like