Reindexing SQL Server Tables
Feb
13
Written by:
2/13/2009 11:53 AM
I had the need to reindex all the tables in a SQL Server 2005 database this week and found some very good scripts that loop through the list of user tables using a cursor and reindex each table to improve performance and reduce the size of the data file. In addition I used the dbcc command to shrink the database after the reindexing had completed. Below is the code used.
USE [dbname]
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
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
EXEC sp_updatestats
DBCC SHRINKDATABASE ([dbname], 10);
GO
3 comment(s) so far...
Re: Reindexing SQL Server Tables
Steve, this is old style code. ALTER index is a much better choice in SQL 2005/2008.
Also, why are you shrinking the database after reindexing?
By Michael John on
4/16/2009 10:59 AM
|
Re: Reindexing SQL Server Tables
I think this code would help a lot in reducing the size of data file which could later be useful and not complicate any process. I will make a try of it.
Thanks for sharing.
By david on
11/23/2009 2:41 AM
|
Re: Reindexing SQL Server Tables
Discussed about the SQL server and more interesting matter discussed in the post because in the post define about the SQL server table so that can be know that and able to understand regarding post.
By Server Hosting on
2/3/2012 2:15 AM
|