用于删除具有系统生成名称的 PK 约束的 SQL Server 2008 脚本

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我正在尝试向 SQL Server 2008 中的现有表添加聚集索引，它需要是一个自动化脚本，因为该表存在于跨多个服务器的多个数据库中.

I am trying to add a clustered index to an existing table in SQL Server 2008, and it needs to be an automated script, because this table exists on several databases across several servers.

为了添加聚集索引，我需要删除表上的 PK 约束，然后将其重新添加为非聚集索引.问题是 PK 约束的名称是自动生成的，并且在末尾附加了一个 guid，因此类似于PK_[Table]_D9F9203400".

In order to add a clustered index I need to remove the PK constraint on the table, and then re-add it as unclustered. The problem is the name of the PK constraint is auto-generated, and there is a guid appended to the end, so it's like "PK_[Table]_D9F9203400."

所有数据库的名称都不同，我不知道如何编写一个自动化脚本，在我不知道约束名称的表上删除 PK 约束.任何帮助表示赞赏！

The name is different across all databases, and I'm not sure how to write an automated script that drops a PK constraint on a table in which I don't know the name of the constraint. Any help is appreciated!

更新:

下面的答案是我用过的.完整脚本:

Answer below is what I used. Full script:

Declare @Val varchar(100)
Declare @Cmd varchar(1000)

Set @Val = (
    select name
    from sysobjects
    where xtype = 'PK'
    and parent_obj = (object_id('[Schema].[Table]'))
)
Set @Cmd = 'ALTER TABLE [Table] DROP CONSTRAINT ' + @Val
Exec (@Cmd)
GO

ALTER TABLE [Table] ADD CONSTRAINT PK_Table
    PRIMARY KEY NONCLUSTERED (TableId)
GO

CREATE UNIQUE CLUSTERED INDEX IX_Table_Column
    ON Table (Column)
GO

推荐答案

你可以查一下约束的名字，写一点动态 SQL 来处理 drop.

You can look up the name of the constraint and write a bit of dynamic SQL to handle the drop.

SELECT name 
    FROM sys.key_constraints 
    WHERE parent_object_id = object_id('YourSchemaName.YourTableName')
        AND type = 'PK';

相关文章