Drop primary key using script in SQL Server database

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

User · Answer

The answer I got is that variables and subqueries  will not work and we have to user dynamic SQL script   The following works   DECLARE  SQL VARCHAR 4000  SET  SQL    ALTER TABLE dbo Student DROP CONSTRAINT  ConstraintName     SET  SQL   REPLACE  SQL    ConstraintName      SELECT   name                                                FROM     sysobjects                                                WHERE    xtype    PK                                                          AND parent obj          OBJECT ID  Student      EXEC   SQL

User · Answer

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

User · Answer

simply click       Database  tables your table name keys copy the constraints like  PK  TableName  30242045    and run the below query is    Query alter Table  TableName  drop constraint PK  TableName  30242045

[sql-server] Drop primary key using script in SQL Server database

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2005