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