alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also, see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
--
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>