SQL Server:删除表主键，不知道它的名字 [英] SQL Server: drop table primary key, without knowing its name

问题描述

使用:SQL Server数据库:北风

Using: SQL Server Database: Northwind

我想删除表主键，但不知道 PK 约束名称..

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

例如，使用Northwind Sample数据库中的Categories表，主键列是'CategoryId'，主键名是'PK_Categories'

eg, 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 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.

我正在尝试:

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?

非常感谢，

鲍勃

推荐答案

为此您必须使用动态 SQL，因为 ALTER TABLE 不接受变量或子查询.

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

这篇关于SQL Server:删除表主键，不知道它的名字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！