Drop All DB Indexes

Hi, All
I need to drop all indexes in a DB and then recreate them with a script. The
recreate is not a problem, but I've struggling to find an example script of
how to drop all user indexes in a DB. I will need to know if the index is
being used to enforce a primary key and if so I'll have to remove any
contraints before attempting the drop.
The other thing I've notice when selecting all tbl's from sysobject where
type='U' it's showing a table called dtProperties which is not a user table.
This table shows as a system table in the enterprise manager. Why is it
specified as 'U' in the sysobjects?
I'd really appreciate any help I can get
Thanks
Antony
If you automate the drop, how will you automate the re-create? The re-create is the difficult part
as you need to know the columns, whether it is unique, whether it is clustered, PK, UNIQUE etc.
Anyhow, some tips on automating the DROP:

> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB.
Use a cursor to loop sysindexes and inside the cursor use dynamic SQL to drop the indexes. Use
INDEXPROPERTY to filter out statistics and hypothetical indexes. Also, OBJECTPROPERTY to filter out
non-system tables.

> I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
Use the status column for this (see Books Online, sysindexes).

> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
Historical screw-up. Use OBJECTPROPERTY and the IsMsShipped option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:C5FC191E-E418-44CA-9DA4-1C4B13C7C4CD@.microsoft.com...
> Hi, All
> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB. I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
> I'd really appreciate any help I can get
> Thanks
> Antony