Rebuilding Indexes

[Vic]

Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.

I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.

Thanks in advance!

[Kristine Greenlee]

Selecting "Reorganize data and index pages" on the Optimizations tab will run DBCC DBREINDEX. Selecting original amount of free space will run DBCC DBREINDEX (tablename, 0). Or selecting a percentage to keep free will run DBCC DBREINDEX(tablename, percentage).

------------
Vic at 9/20/00 9:59:00 AM

Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.

I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.

Thanks in advance!

Try this reindexing stored procedure
It will rebuild all indexes it finds
I hope it will be helpful.
--@lv prefixes locally declared variables
~Irene~

CREATE PROCEDURE spRebuildTableIndexes
AS
DECLARE @lvTableName varchar(30)
DECLARE TableCursor CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'Base Table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @lvTableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @lvTableName
DBCC REINDEX(@lvTableName)
FETCH NEXT FROM TableCursor INTO @lvTableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
RETURN


------------
Kristine Greenlee at 9/20/00 10:21:13 AM

Selecting "Reorganize data and index pages" on the Optimizations tab will run DBCC DBREINDEX. Selecting original amount of free space will run DBCC DBREINDEX (tablename, 0). Or selecting a percentage to keep free will run DBCC DBREINDEX(tablename, percentage).

------------
Vic at 9/20/00 9:59:00 AM

Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.

I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.

Thanks in advance!

[Kristine Greenlee]

Here's another way:

exec sp_MSForEachTable "DBCC DBREINDEX ('?&#39"

------------
at 9/25/00 2:32:46 PM

Try this reindexing stored procedure
It will rebuild all indexes it finds
I hope it will be helpful.
--@lv prefixes locally declared variables
~Irene~

CREATE PROCEDURE spRebuildTableIndexes
AS
DECLARE @lvTableName varchar(30)
DECLARE TableCursor CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'Base Table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @lvTableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @lvTableName
DBCC REINDEX(@lvTableName)
FETCH NEXT FROM TableCursor INTO @lvTableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
RETURN


------------
Kristine Greenlee at 9/20/00 10:21:13 AM

Selecting "Reorganize data and index pages" on the Optimizations tab will run DBCC DBREINDEX. Selecting original amount of free space will run DBCC DBREINDEX (tablename, 0). Or selecting a percentage to keep free will run DBCC DBREINDEX(tablename, percentage).

------------
Vic at 9/20/00 9:59:00 AM

Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.

I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.

Thanks in advance!