CODEWITHSUNDEEP
sql-serverconstraintsprimary-keyrename
SE_VS_NET
SE_VS_NET

Reputation: 129

How to rename keys ("PK_") and constraints ("DF_") automatically upon renaming table and/or column in SQL Server?

As you know, when we set a PK and/or constraint, a key/constrains name will be automatically created for us (PK_..., DF_...). But whenever I rename a column name and/or table name, those keys and constraints aren't automatically renamed accordingly.

Is there any setting in SQL that allow it to do it automatically?

Upvotes: 2

Views: 487

Answers (2)

Dmitry Shashurov
Dmitry Shashurov

Reputation: 1724

Instead of one table renaming query

EXEC sp_rename 'OldName', 'NewName'

You can to get a list of renaming queries (for table and constraints):

declare
 @old nvarchar(100),
 @new nvarchar(100)
set @old = 'OldName'
set @new = 'NewName'
select 'EXEC sp_rename ''' + name + ''', ''' + 
  REPLACE(name, @old, @new) + ''''
 from sys.objects 
 where name like '%' + @old + '%'

And then execute each of the query, that you receive in this list.

This works great for renaming tables in MSSQL!

Upvotes: 0

Shiva
Shiva

Reputation: 20955

AFAIK there is no setting. You will have to use sp_rename to rename the constraints after you rename the column name and/or table name.

Related Reading: http://davidbrycehoward.com/archive/2011/01/naming-and-renaming-database-constraints/

Upvotes: 2

Related Questions