How-To: Automatically Rename Foreign Keys on a DB

• Download source - 914 B

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. :)

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. :)

History

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