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

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开发者_StackOverflow社区, 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

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

Adding to Stuart Ainsworth answer, I do not know if PK name has to be unique across different schemas (if so, that answer is ok). Anyway I would choose different sub query for PK name, allowing explicitly to define schema:

declare @PrimaryKeyName sysname = 
    (select CONSTRAINT_NAME 
     from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     where CONSTRAINT_TYPE = 'PRIMARY KEY' and TABLE_SCHEMA='dbo' and TABLE_NAME = 'PKTest'
    )

IF @PrimaryKeyName is not null
begin
    declare @SQL_PK NVARCHAR(MAX) = 'alter table dbo.PKTest drop constraint ' + @PrimaryKeyName
    print (@SQL_PK)
    EXEC sp_executesql @SQL_PK;
end