How do I rename my constraints

Question

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 I'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.

this is not anal, when you get a constraint error, it is nice to have the actual table name and column as part of the constraint name, so you can figure out the problem. if the constraint name has the wrong table name in it, that will just cause you some delay in figuring out the problem. — KM., Sep 16 '10 at 17:46

Denis Valeev · Accepted Answer · 2010-09-16T17:51:26.120

35

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

edited Sep 16 '10 at 17:51

answered Sep 16 '10 at 17:40

Denis Valeev

5,975
35
41

FYI,Bug is in closed status in given link. – ManirajSS Oct 23 '14 at 09:20
4

For those using `sp_rename` with objects that have a dot in the name (ala EntityFramework-created constraints), you will need to bracket-up the `@objname` parameter, as well as including `'OBJECT'` as the `@objtype` parameter: `exec sp_rename @objname = '[dbo].[FK_dbo.TableA_dbo.TableB_ColumnOne]', @newname = 'FK_dbo.TableA_dbo.TableC_ColumnOne', @objtype = 'object';` – dperish Aug 25 '16 at 16:43
From the documentation: *Quotation marks are only necessary if a qualified object is specified.* *If the object to be renamed is a constraint, object_name must be in the form schema.constraint.* *When renaming a constraint, the schema to which the constraint belongs must be specified.* Here is an example, note that the table name is not included: `exec sp_rename 'schema.OldName', 'NewName', 'object'` – pasx Jul 15 '20 at 09:40

score 5 · Answer 2 · answered Sep 23 '13 at 08:29

5

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

answered Sep 23 '13 at 08:29

ozz

5,098
1
50
73

score 5 · Answer 3 · answered Oct 14 '14 at 17:55

5

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 )

answered Oct 14 '14 at 17:55

foxfire

101
2
3

Very useful. What is the " != 'dtproperties' " part for? – Jahaziel Jan 31 '20 at 17:11
This is to prevent the dtproperties table from being considered – foxfire Sep 02 '21 at 21:26

score 2 · Answer 4 · answered Apr 26 '13 at 17:32

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;

score 1 · Answer 5 · answered Jul 02 '20 at 15:57

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

score 0 · Answer 6 · answered May 13 '14 at 14:41

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;

score 0 · Answer 7 · answered Sep 16 '10 at 17:56

0

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.

answered Sep 16 '10 at 17:56

Ashish Patil

818
7
15

Interesting idea, but in my situation I have to create a script that can be used to make the same changes on other copies of the database. Incidentally, its less than 10 tables, so not a huge deal. – Matt Casto Sep 16 '10 at 18:21
Ok. I had to use such method when one of my MySQL DB was migrated from Windows to Amazon RDS. We has all table names in upper case, while the DUMP we got from windows had all lower case. And on RDS, and under linux, MySQL table names are case sensitive. – Ashish Patil Sep 17 '10 at 10:42

score 0 · Answer 8 · answered May 05 '17 at 20:48

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

How do I rename my constraints

8 Answers8

Linked