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

You need to use dynamic SQL to execute the ALTER TABLE statement if you want to parameterize the constraint name. Most of the DDL statement parameters in TSQL cannot be parameterized by using variables or expressions. So do below instead:

declare @.const nvarchar(200)

set @.const = QUOTENAME((

select kc.name

from sys.key_constraints as kc

where kc.parent_object_id = object_id('tablename') and kc.type = 'PK'

))

exec('ALTER TABLE tablename DROP CONSTRAINT ' + @.const)

|||

Hi Umachandar,

your hint worked well - thanks a lot for helping me out :-)

Adunak