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.
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.
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';
exec sp_rename 'schema.OldName', 'NewName', 'object'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; 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 