How to rebuild index ?

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
DanielOne 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_SCHEMA) +
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_Command, 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
>|||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/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||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 .