Drop primary key using script in SQL Server database [duplicate]

Question

I need to drop the primary key of a table Student in a SQL Server database.

I have edited in the table and the script I got is

ALTER TABLE dbo.Student     DROP CONSTRAINT PK__Student__9CC368536561EF8B 

But when I run this script in SQL Server query browser to drop the primary key

It shows the message

Msg 3728, Level 16, State 1, Line 1
'PK__Student__9CC368536561EF8B' is not a constraint.
Msg 3727, Level 16, State 0, Line 1

To my concern I think PK__Student__9CC368536561EF8B this will be generated randomly please help me to drop the primary key constraint using script.

Thanks in advance

Answer 1

You can look up the constraint name in the sys.key_constraints table:

SELECT name FROM   sys.key_constraints WHERE  [type] = 'PK'        AND [parent_object_id] = Object_id('dbo.Student'); 

If you don't care about the name, but simply want to drop it, you can use a combination of this and dynamic sql:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);  SELECT @table = N'dbo.Student';  SELECT @sql = 'ALTER TABLE ' + @table      + ' DROP CONSTRAINT ' + name + ';'     FROM sys.key_constraints     WHERE [type] = 'PK'     AND [parent_object_id] = OBJECT_ID(@table);  EXEC sp_executeSQL @sql; 

This code is from Aaron Bertrand (source).