Delete Primary key constraint without knowing the name. SQL 2008
Posted on 2009-04-16
Using a single SQL 2008 query, I'm trying to delete a primary key constraint from a table. but the catch is I don't know the name of the primary key. In this case, each table has a random number attached to the end of the pk....like, pk_WorkFlowDocumentsTable_234123. So I just need a query that will delete the pk constraint, no matter what the name is.
I'm very close...
This query returns the name of the key i need to delete:
select a.CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME where a.table_name = 'test2_WorkFlowDocumentsTable' and constraint_type = 'Primary key'
This query will delete the pk constraint:
ALTER TABLE test2_WorkFlowDocumentsTable DROP CONSTRAINT [name]
But I just can't figure out how to put the two together?