How to drop all PKs on tables in database?

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanksdeclare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||I think it would be safer to do this inside the loop:

print 'alter table ' + @.Table + ' drop constraint ' + @.PK

rather than this:

exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )

that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.|||the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
1 step first
drop all foreign keys relation ship
declare @.Table varchar(100)
declare @.FK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',...)
open cur
fetch next from cur into @.FK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.FK )
fetch next from cur into @.FK, @.Table
end
close cur
deallocate cur
2. step second now drop all primary key relation ship
declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||right... we need to drop the FKs first.
but the code from greenindia is having the following problems

1) xtype for foriegn-key is "F" and not "FK"
2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
object_name(parent_obj) in ('Table1','Table2',...)

u need a trip from sysforeignkeys to trap the relation properly :rolleyes:|||you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?

What would be the fun in that?|||Hi all,

Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
print ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur

print 'alter table ' + @.Table + ' drop constraint ' + @.PK|||does this query return anything? if not, there's your answer.

select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')