SQL Server: drop table primary key, without knowing its name

Question

HI,

Using: SQL Server Database: Northwind

I'd like to drop a table primary key, without knowing the PK constraint name..

eg, using the Categories table in the Northwind Sample database, the primary key column is 'CategoryId', and the primary key name is 'PK_Categories'

I can drop the primary key while knowing the primary key name:

ALTER TABLE categories DROP CONSTRAINT PK_Categories;

And I can also get the primary key name for the table by table name:

select name from sysobjects where xtype = 'PK' and parent_obj = object_id('categories')

However, I cannot put them together to delete a table's primary key, without first knowing the primary key name.

I am trying:

ALTER TABLE categories DROP CONSTRAINT


(select name from sysobjects where xtype = 'PK' and parent_obj = object_id('categories') ) 

Can anyone show me where I am going wrong?

Many thanks,

Bob

Answer 1

You'll have to use dynamic SQL for that, since ALTER TABLE does not accept variables or subqueries.

CREATE TABLE PKTest ( ID INT PRIMARY KEY ) ;

DECLARE @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE PKTEST DROP CONSTRAINT |ConstraintName| '

SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT   name
                                               FROM     sysobjects
                                               WHERE    xtype = 'PK'
                                                        AND parent_obj = OBJECT_ID('PKTest')
                                             ))

EXEC (@SQL)

DROP TABLE PKTest