To rename primary keys simply PK_TableName :
CREATE PROCEDURE dbo.Rename_PrimaryKeys @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''PK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') + ''', ''OBJECT'';' FROM sys.key_constraints WHERE type = 'PK' AND name <> 'PK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') AND OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO
To rename an FK with the schema FK_TableName_col_col_ReferencedName_col_col :
CREATE PROCEDURE dbo.Rename_ForeignKeys_WithColumns @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''FK_' + REPLACE(OBJECT_NAME(fk.parent_object_id), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + '_' + REPLACE(OBJECT_NAME(fk.referenced_object_id), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + ''', ''OBJECT'';' FROM sys.foreign_keys AS fk WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO
For foreign keys, if you just want FK_TableName_ReferencedName , then this is much simpler:
CREATE PROCEDURE dbo.Rename_ForeignKeys @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''FK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') + '_' + REPLACE(OBJECT_NAME(referenced_object_id), '''', '') + ''', ''OBJECT'';' FROM sys.foreign_keys WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO
For indexes, this will rename any indexes IX_TableName_Col1_Col2... It will ignore primary keys (as they are discussed separately above), add UQ_ to unique indexes / constraints (so IX_UQ_TableName_Col1_Col2... will handle unique constraints and unique indexes in the same way and will ignore included columns. Ignoring included columns can lead to name conflicts if you have redundant indexes that differ only in the columns included.)
CREATE PROCEDURE dbo.Rename_Indexes @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(i.name, '''', '''''') + ''', ''IX_' + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE '' END + REPLACE(OBJECT_NAME(i.[object_id]), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id AND ic.[object_id] = c.[object_id] WHERE ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND is_included_column = 0 ORDER BY ic.index_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''', ''OBJECT'';' FROM sys.indexes AS i WHERE index_id > 0 AND is_primary_key = 0
For default restrictions:
CREATE PROCEDURE dbo.Rename_DefaultConstraints @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(dc.name, '''', '''''') + ''', ''DF_' + REPLACE(OBJECT_NAME(dc.parent_object_id), '''','') + '_' + REPLACE(c.name, '''', '') + ''', ''OBJECT'';' FROM sys.default_constraints AS dc INNER JOIN sys.columns AS c ON dc.parent_object_id = c.[object_id] AND dc.parent_column_id = c.column_id AND OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO
And finally, check the limitations:
CREATE PROCEDURE dbo.Rename_CheckConstraints @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(cc.name, '''', '''''') + ''', ''CK_' + REPLACE(OBJECT_NAME(cc.parent_object_id), '''','') + '_' + REPLACE(c.name, '''', '') + ''', ''OBJECT'';' FROM sys.check_constraints AS cc INNER JOIN sys.columns AS c ON cc.parent_object_id = c.[object_id] AND cc.parent_column_id = c.column_id AND OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO
Please note that PRINT will not necessarily show the entire operator, depending on your settings for the results in the text and the size of the instruction. But it should be good enough for the eyeball that the scripts do the right job. I set them all to PrintOnly by default.