"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