如何重命名约束

尝试:

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

另外,在处理非默认值时,在重命名这些东西方面也有一个bug schema .

Cannot rename a default constraint for a table in a schema which is not dbo by rsocol @Microsoft Connect

经过进一步的挖掘,我发现它实际上是这样的:

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

Source

我不是一个很大的游标球迷,这可以写得更简单。

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 ) 

如果有人感兴趣,我只需要将审计字段“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的脚本。生成脚本以重命名数据库中的所有默认值。用于更正在创建时未显式命名的约束。

 --
-- 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;

我知道这个帖子有点过时了,但我想发布我的替代@foxfire的答案,因为我对它做了一些修改。当我运行到一个有太多重命名的数据库时,它使用了一小部分名称,这使得@sql被截断。我还为break-out添加了错误处理,并为处理dbo以外的不同模式添加了模式名。我选择不使用begintry,这样就可以在多个sqlserver版本中使用它。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

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

如果要重命名的对象是约束,则对象名称的格式必须为架构约束.

所以正确的形式是:

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

不要用架构作为新名称的前缀。。。