如何重命名我的约束条件？

Question

我在数据库中重命名了一个表，使用的语句是

EXEC sp_rename 'tblOldAndBusted', 'tblNewAndShiny'

所有外键约束都已更新为新表名，但它们的名称仍基于旧表名。例如，现在我有FK_tblOldAndBusted_tblTastyData，但我想要FK_tblNewAndShiny_tblTastyData。是否有简单的方法来脚本化这个过程？

另外，我是不是太严格了？我知道数据库在约束中使用旧表名也可以正常工作，但感觉像破窗效应。

- Matt Casto

---

这并不是关于肛交的问题，当你遇到约束错误时，能够将实际的表名和列名作为约束名的一部分会很有帮助，这样你就可以找出问题所在。如果约束名中包含错误的表名，那么这只会导致你花费更长时间来解决问题。 - KM.

那不是肛交，那是刑罚。 - Denis Valeev

Answer 1

尝试：

exec sp_rename 'FK_tblOldAndBusted_tblTastyData', 'FK_tblNewAndShiny_tblTastyData', 'object'

此外，在处理非默认 schema 时重命名此类事物存在错误。

无法为不属于 dbo 的模式中的表重命名默认约束。由 rsocol @Microsoft Connect

- Denis Valeev

---

请注意，给定链接中的Bug已处于关闭状态。 - ManirajSS

如果您使用sp_rename更改带有点号的名称（例如由EntityFramework创建的约束），则需要在@objname参数加上方括号，并在@objtype参数中包含'OBJECT'：exec sp_rename @objname = '[dbo].[FK_dbo.TableA_dbo.TableB_ColumnOne]', @newname = 'FK_dbo.TableA_dbo.TableC_ColumnOne', @objtype = 'object'; - dperish

从文档中得知： 只有在指定了限定对象时才需要使用引号。 如果要重命名的对象是一个约束，则 object_name 必须采用 schema.constraint 的形式。 重命名约束时，必须指定约束所属的模式。 以下是一个示例，请注意表名未包含在内： exec sp_rename 'schema.OldName', 'NewName', 'object' - pasx

Answer 2

在进一步查找后，我发现它实际上必须是这种形式：

EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint', N'OBJECT'

Source

- ozz

Answer 3

我不是游标的铁粉，这段代码可以更简单地编写。

DECLARE @SQLCmd varchar(MAX) = ''
SELECT 
    @SQLCmd += 'EXEC sp_rename ''' + dc.name + ''', ''DF' + 
                OBJECT_NAME( dc.parent_object_id ) + c.name + ''', ''OBJECT'';'
FROM 
    sys.default_constraints dc
    JOIN sys.columns c 
        ON c.object_id = dc.parent_object_id 
        AND c.column_id = dc.parent_column_id
WHERE 
    dc.name != 'DF' + object_name( dc.parent_object_id ) + c.name 
    AND OBJECT_NAME( dc.parent_object_id ) != 'dtproperties'
EXEC( @SqlCmd ) 

- foxfire

---

非常有用。" != 'dtproperties' " 部分是什么意思？ - Jahaziel

这是为了防止将dtproperties表视为考虑因素。 - foxfire

Answer 4

如果有人感兴趣，我刚刚不得不为名为"EnteredDate"的审计字段重命名所有默认约束条件，以特定的模式进行更新和替换。希望这可以帮助并成为一个起点。

DECLARE @TableName VARCHAR(255), @ConstraintName VARCHAR(255)
DECLARE constraint_cursor CURSOR
    FOR 
        select b.name, c.name from 
        sys.all_columns a 
        inner join
        sys.tables b 
        on 
        a.object_id = b.object_id
        inner join
        sys.default_constraints c
        on a.default_object_id = c.object_id
        where 
            b.name <> 'sysdiagrams'
            and a.name = 'EnteredDate' -- column name
            and b.type = 'U'

OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @SqlScript VARCHAR(255) = ''
        SET @SqlScript = 'sp_rename ' + @ConstraintName + ', ''DF_' + @TableName + '_EnteredDate'', ''object'''
        EXEC(@SqlScript)
        SELECT @TableName, @ConstraintName, 'DF_' + @TableName + '_EnteredDate', @SqlScript 
        FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName
    END 
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;

- user906573

Answer 5

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15

如果要重命名的对象是一个约束条件，object_name 必须采用 schema.constraint 的形式。

因此，正确的形式是：

exec sp_rename 'schema.oldname','newname', 'object'

在原名称前加上模式。

不要在新名称前加上模式...

- pasx

Answer 6

基于用户906573的脚本。生成一个脚本来重命名数据库中的所有默认项。对于在创建时没有明确命名的约束条件进行修正非常有用。

 --
-- Generates a script to rename defaults to the pattern DF_tablename_columnname
--

DECLARE @TableName VARCHAR(255), @ConstraintName VARCHAR(255), @ColumnName varchar(255), @SchemaName varchar(255)
DECLARE constraint_cursor CURSOR
    FOR 
        select b.name, c.name, a.name, sc.name
        from sys.all_columns a 
        inner join sys.tables b on a.object_id = b.object_id
        join sys.schemas sc on b.schema_id = sc.schema_id
        inner join sys.default_constraints c on a.default_object_id = c.object_id
        where 
            b.name <> 'sysdiagrams'
            and b.type = 'U'

OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName, @ColumnName, @SchemaName

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @SqlScript VARCHAR(255) = ''
        SET @SqlScript = 'sp_rename ''' + @SchemaName + '.' + @ConstraintName + ''', ''' + @SchemaName + '.DF_' + @TableName + '_' + @ColumnName + ''', ''object''' + char(13) + char(10) + 'GO' + char(13) + char(10)
        --EXEC(@SqlScript)
        print @sqlscript
        FETCH NEXT FROM constraint_cursor INTO @TableName, @ConstraintName, @ColumnName, @SchemaName
    END 
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;

- Mark Challen

Answer 7

如果需要重命名的太多，那么考虑导出到转储文件，使用任何文本编辑器编辑该转储文件以替换表名，然后从转储文件中恢复。我的意思是只导出约束条件的转储文件，而不是全部导出。

- Ashish Patil

---

有趣的想法，但在我的情况下，我必须创建一个脚本，可以用来对其他副本进行相同的更改。顺便说一句，这不到10个表，所以不是很大的问题。 - Matt Casto

好的。当我的一个MySQL数据库从Windows迁移到Amazon RDS时，我不得不使用这种方法。我们所有的表名都是大写的，而从Windows获得的DUMP则全部是小写的。在RDS上，并且在Linux下，MySQL表名是区分大小写的。 - Ashish Patil

Answer 8

我知道这个帖子有点过时了，但我想发布我的替代方案，以取代@foxfire的答案，因为我对它进行了一些修改。我将其改为只使用较小的名称块，因为我遇到了数据库，其中重命名太多，导致@sql被截断。我还添加了错误处理来退出，以及用于处理不同模式（而非dbo）的模式名称。我选择不使用begin try，以便它可以在多个SQL Server版本中使用。where子句可以操纵以满足OP的原始意图。

BEGIN TRAN

DECLARE @sql varchar(MAX) = '...'

WHILE LEN(@sql) > 0 BEGIN
       SET @sql = '';

       SELECT TOP 50 @sql = @sql 
              + 'EXEC sp_rename N''' + SCHEMA_NAME(dc.[schema_id]) + '.' + dc.name 
              + ''', N''DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name 
              + ''', ''OBJECT'';' + CHAR(10)
       FROM sys.default_constraints dc
       inner join sys.columns c 
              ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
       WHERE dc.name LIKE 'DF[_][_]%' -- rename any auto named defaults

       PRINT @sql
       EXEC(@sql)

       IF @@ERROR <> 0 BEGIN 
              IF @@TRANCOUNT > 0 ROLLBACK TRAN
              BREAK;
       END
END

IF @@TRANCOUNT > 0 COMMIT TRAN
--IF @@TRANCOUNT > 0 ROLLBACK TRAN

- SpaceGhost440