Click here to Skip to main content
Click here to Skip to main content

How-To: Automatically Rename Foreign Keys on a DB

, 27 Aug 2008
Rate this:
Please Sign up or sign in to vote.
Use T-SQL and system views/stored procedures to give consistent names to every relation in a database

Introduction

This article explains how to automatically rename every relation in your database.
It could be useful if your database was upgraded from a different DBMS and the relation names are meaningless (like the Access upgrade does), or if those names have been created years ago by different developers using different standards, or if you renamed one or more tables in your database and you need to fix foreign keys' names also.

Background

The idea (and the underlying algorithm) is simple:

Take all the relations in the database, look at the tables involved in the relation and give each one the name "FK_ParentTable_ForeignTable[Counter]".

With previous versions of SQL Server, it was easier because the user could directly update (with a single statement) system catalogues, but in SQL Server 2005 this feature was disabled for consistency reasons.

In SQL Server 2005, there are a lot of useful views lying over the system catalogues that let the user know about everything in every database. The code uses those views to accomplish the task.

Using the Code

The code is just a T-SQL block of code, so you can:

  • paste it in a "Management Studio" window and run it from there.
  • put it as a Stored Procedure body to call when needed.
  • run from within a "database update" script.
  • ... do whatever you would do to run a SQL batch.

Points of Interest

This code makes use of some new SQL Server 2005 features.

To make the code simpler, it was divided logically using Common table expressions (CTE).
Moreover, to count the foreign keys properly, a ranking function is used.
So if you are new to these, you can learn something. Smile | :)

In Depth Look

The logic is simple: obtain a list of actual foreign keys on a DB and rename them using the sp_rename extended procedure. So the code is basically a query wrapped around a procedure code that loops on the result set and does the rename work. There's nothing important / special / difficult to point out in the procedure... the interesting part is the query that is explained in detail below.

First of all, we need to obtain every foreign key present in our database.
The view sys.foreign_key_columns has the information on "what column is linked to what other column". We use this view to have the list of every distinct relation (a relation could take more than one column). The first CTE has this information.

Next, we should translate object IDs into object names.
This can be done joining the first CTE with the sys.objects view.
Additionally, we can count how many times a parent is related to a referenced table.

This CTE stores:

  • the actual relation name
  • the parent table
  • the referenced table, and
  • the counter

The third step is to translate the information obtained in the second step to a more useful thing: Old relation name and New relation name.
The CASE is used to put or omit the counter if there is more than one relation or only one (you can easily modify it if you want a different renaming scheme).

The fourth step is used to take into consideration (for the rename process) only the relation names that don't already exist (because maybe someone has already fixed some of them manually or they were created with the right name).

Any hints/comments are welcome.. and if you find this article useful, don't forget to rate it. Smile | :)

History

I always hated history.. I prefer what is still to come.. Smile | :)

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

About the Author

Lino Barreca
Software Developer (Senior)
Italy Italy
No Biography provided

Comments and Discussions

 
GeneralI found one flaw in script (regarding schemas) Pinmemberptako10-Sep-08 20:50 
GeneralWhy Would You Want To Pinmemberdeveloper62-Sep-08 21:52 
I would have found it interesting if you had stated the reason why you would want to rename all foreign key constraints to conform to some standard.
AnswerRe: Why Would You Want To PinmemberLino Barreca5-Sep-08 1:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 27 Aug 2008
Article Copyright 2008 by Lino Barreca
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid