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

by

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).

More Related Contents:

  1. SQL Server query to find all permissions/access for all users in a database
  2. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  3. Why use the INCLUDE clause when creating an index?
  4. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  5. Is it possible to force row level locking in SQL Server?
  6. Regular Expressions in SQL Server servers?
  7. SQL Server GUID sort algorithm. Why?
  8. What happens to the primary key Id when it goes over the limit?
  9. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  10. SQL Server recursive query
  11. nvarchar(max) vs NText
  12. INFORMATION_SCHEMA vs sysobjects
  13. “Prevent saving changes that require the table to be re-created” negative effects
  14. What are row, page and table locks? And when they are acquired?
  15. Table Valued Function Killing My Query Performance
  16. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  17. How to add Active Directory user group as login in SQL Server
  18. How do you specify a different port number in SQL Management Studio?
  19. An attempt was made to access a socket in a way forbidden by its access permissions
  20. Should every User Table have a Clustered Index?
  21. Aggregate bitwise-OR in a subquery
  22. How to set monday as first day of week in SQL Server
  23. How to find SQL Server running port?
  24. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  25. Changing SQL Server collation to case insensitive from case sensitive?
  26. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  27. Database design: Calculating the Account Balance
  28. How to store directory / hierarchy / tree structure in the database?
  29. Merge multiple rows into a single row
  30. Does query plan optimizer works well with joined/filtered table-valued functions?

Leave a Comment