MS SQL Server - How to rebuild index ?

Author

How to rebuild index ?

Peter

2006-10-24, 6:30 pm

Hi,

We are using SQL Server 2000.

A developer asks us to rebuild the index of the database. However, from the
BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
a particular table.

Is there any command to do so ? So far as I know, rebuild index of the
database is part of the Database Maintenance Plan.

Thanks
Daniel

Dan Guzman

2006-10-24, 6:30 pm

One method is to generate and execute DBCC DBREINDEX commands using a script
like the example below.

DECLARE @DBCC_Command nvarchar(4000)
DECLARE DBCC_Commands
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'DBCC DBREINDEX(''' +
QUOTENAME(TABLE_SCHE
MA) +
N'.' +
QUOTENAME(TABLE_NAME
) +
''')'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(
TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME
)),
'IsMSShipped') = 0
OPEN DBCC_Commands
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DBCC_Commands INTO @DBCC_Command
IF @@FETCH_STATUS = -1 BREAK
RAISERROR(@DBCC_Comm
and, 0, 1) WITH NOWAIT
EXEC(@DBCC_Command)
END
CLOSE DBCC_Commands
DEALLOCATE DBCC_Commands

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Peter" <Peter@discussions.microsoft.com> wrote in message
news:%23OsDDCj3GHA.1608@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> We are using SQL Server 2000.
>
> A developer asks us to rebuild the index of the database. However, from
> the BOL, I find that the command DBCC DBREINDEX can be used to rebuild
> index for a particular table.
>
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
>
> Thanks
> Daniel
>
>

Tracy McKibben

2006-10-24, 6:30 pm

Peter wrote:
> Hi,
>
> We are using SQL Server 2000.
>
> A developer asks us to rebuild the index of the database. However, from the
> BOL, I find that the command DBCC DBREINDEX can be used to rebuild index for
> a particular table.
>
> Is there any command to do so ? So far as I know, rebuild index of the
> database is part of the Database Maintenance Plan.
>
> Thanks
> Daniel
>
>

There is no index "of the database", indexes are on tables. I suspect
you were asked to rebuild all of the indexes IN the database, possibly
to remove fragmentation to address a performance problem.

Have a look here:
http://realsqlguy.com/serendipity/a...-A-Wall....html

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Paul Ibison

2006-10-24, 6:30 pm

Peter,

to reindex all tables in a database you could use:

EXEC sp_msForEachTable 'DBCC DBREINDEX ("?")'
The maintenance plan will also give this functionality. I'd recommend
reading up on the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG as
the former command is essentially an offline operation and if you have a
24/7 operation you'll need to consider the latter.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Sponsored Links