How To Remove A Primary Key You Don't Know?

Hello Guys,

I need some help with a question I've been struggling to solve all day. I'm new to Transact-SQL and thus tried to solve my little problem by reading the language reference, but I didn't find what I was looking for.

Here's the scenario: I want to remove a table's primary key and replace it with a new one. Removing the pk is usually done with:

1) ALTER TABLE tablename DROP CONSTRAINT constraint_name; GO

Unfortunatley, I don't know constraint_name and the simple MySQL command

ALTER TABLE tablename DROP PRIMARY KEY

doesn't seem to work. To get constraint_name I made a query:

2) SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename')

Now, here's the core of my problem: I need to link statements 1) and 2) and tried

ALTER TABLE tablename DROP CONSTRAINT (SELECT name from sys.key_constraints where parent_object_id = (select object_id from sys.tables where name = 'tablename'))

but that doesn't, I get a syntax error.

Do you have a clue? I'm sure its a simple problem for an experienced programmer, but I don't get it. Or do you know another way of removing the primary key without having to state constraint_name explicitely?

Any help is appreciated!

Thanks a bunch,

adunak