問題描述

我正在嘗試向 SQL Server 2008 中的現(xiàn)有表添加聚集索引，它需要是一個自動化腳本，因為該表存在于跨多個服務(wù)器的多個數(shù)據(jù)庫中.

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

所有數(shù)據(jù)庫的名稱都不同，我不知道如何編寫一個自動化腳本，在我不知道約束名稱的表上刪除 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

推薦答案

你可以查一下約束的名字，寫一點動態(tài) 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';

這篇關(guān)于用于刪除具有系統(tǒng)生成名稱的 PK 約束的 SQL Server 2008 腳本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！