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

Question

Using: SQL Server Database: Northwind

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

E.g., 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 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') ) 

Where am I going wrong?

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

Answer 2

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

Answer 3

I have a different script using sys.key_constraints

USE [my database];


declare @PrimaryKeyCheck nvarchar(max)=null
SELECT @PrimaryKeyCheck = 'alter table ' 
  + quotename(object_schema_name(kc.parent_object_id)) 
  + '.' 
  + quotename(object_name(kc.parent_object_id)) 
  + ' drop constraint ' + quotename(kc.name)
  FROM sys.key_constraints kc
            WHERE type = 'PK'
              AND kc.parent_object_id = OBJECT_ID('dbo.pktest')


IF @PrimaryKeyCheck is not null
begin
    print (@PrimaryKeyCheck)
    --EXEC sp_executesql @PrimaryKeyCheck;
end