how to drop all keys & indexes

[Woody Rao]

I'm trying to drop all indexes and primary keys so that i can rebuild them
(from a script created from same database on another server).

when i go to the 'generate sql scripts', it has the ability to drop or
generate all
tables. it also has the ability to generate all keys only. but i cant find
a way
to drop all of these keys...

any ideas?

tia
woody rao

[Simon Hayes]

"Woody Rao" <nv*****@dreamscape.com> wrote in message
news:10*************@corp.supernews.com...

I'm trying to drop all indexes and primary keys so that i can rebuild them
(from a script created from same database on another server).

when i go to the 'generate sql scripts', it has the ability to drop or
generate all
tables. it also has the ability to generate all keys only. but i cant find a way
to drop all of these keys...

any ideas?

tia
woody rao

One way is to copy and paste the result of a query like this one (assuming
all tables are owned by dbo):

select 'alter table dbo.'+ TABLE_NAME + ' drop constraint ' +
CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE in ('PRIMARY KEY', 'FOREIGN KEY')

This can be automated with a cursor and EXEC() if necessary, although you
may find it easier to look at a database comparison tool which will build
migration/synchronization scripts for you.

Simon