How do I rename my constraints

I renamed a table in my database with

EXEC sp_rename 'tblOldAndBusted', 'tblNewAndShiny'

and all the foreign key constraints were updated to the new table name, but they're still named based on the old table name. For example, I now have FK_tblOldAndBusted_tblTastyData but 开发者_如何学CI'd like to have FK_tblNewAndShiny_tblTastyData.

Is there an easy way to script this?

Also, am I being too anal? I know the database works fine with the old table name in the constraints, but it feels like broken windows.

Try:

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

Also, there is a bug regarding renaming such things when you deal with non-default schema.

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

After some more digging, I found that it actually has to be in this form:

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

Source

I am not a big fan of cursors and this can be written much more simply.

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 ) 

If anyone is interested, I just had to rename all the default constraints for the an audit field named "EnteredDate"to a specific pattern. Update and replace as needed. I hope this helps and might be a starting point.

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;

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

If the object to be renamed is a constraint, object_name must be in the form schema.constraint.

So the correct form is:

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

Prefix the oldname with the schema.

Do not prefix the newname with the schema...

If there are too many to rename, how about export to dump, edit the dump in any text editor of choice to replace the table name, and then restore from dump? I mean export the dump of only constraints, not all.

Based user906573's script. Generate a script to rename all defaults in the database. Useful for correcting constraints that weren't explicitly named at create time.

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

I know this thread is a bit dated but I wanted to post my alternative to @foxfire's answer as I made some modifications to it. Made it take a smaller chunk of the names as I ran into database where there were so many renames it made the @sql truncate. I also added error handling to break out as well as schema names for handling different schemas other than dbo. I chose not to use a begin try so it could be used among multiple sql server versions. The where clause can be manipulated to fulfill the OP's original intent.

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