0

In SQL Server 2014 we need to change naming convention for indexes and keys. The old naming convention is not following any standards. My questions are:

  • How can we change the naming convention for 200 tables in a production server?
  • Can the naming be changed at once using some scripts, or do we have to change them one by one?

Edit: Keys: Primary or Foreign Key

Improve this question
  • 1
    mssqltips.com/sqlservertip/2709/… – Aaron Bertrand Apr 6 '17 at 21:19
  • Take care to test thoroughly when renaming objects, even indexes. You could break code that includes explicit index hints. You can scan views/sprocs/other in the DB to update references automatically, but any ad-hoc SQL elsewhere in apps that use the DB may be harder to reliably find & fix. – David Spillett Dec 12 '19 at 15:09
2

You can use sp_rename to change the name of procedures, columns, tables, indexes, constraints. You'll have to do each object individually (although you can create a single script that renames the objects), and I would highly recommend running through a test environment first to ensure that you do not run into any problems.

Improve this answer
2

To add a bit to Nic's answer:

You should be able to query sys.indexes to get all the current index names; then you can create a list of the old and new names, and use a query or a regular expression to change your list into your script.

NOTE: When you talk about changing the names of keys, do you mean of the primary or foreign key constraint? or, changing the name of the columns that represent keys? Changing the index/constraint names is relatively safe, unless you have query hints that explicitly refer to them, or code that drops and recreates them for some reason. However, changing columns names is more complicated. Those have to be updated everywhere - not just in the table, but in stored procedures, functions, jobs, external scripts (SSIS packages, etc.).

The testing process if changing columns names needs to be much more thorough (as you should exercise every process that interactions with the tables in question), and it's much more likely that you'll encounter an issue at some point, in spite of testing. In an environment where there's very little external code interacting with your database, this might be worth doing; if a large application, or multiple applications, use the database, or if there's a lot of end-user ad-hoc queries out there, I would need a better reason to change things than not following a naming convention. (Of course, if someone brought the system down due to a mistake resulting from not having a naming convention, that can be a pretty good reason).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.