Automatically rename Foreign Keys on a DB

Introduction

This article explains how to rename automatically 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 in years from different developers using different standards or if you renamed one or more table 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 properly the foreign keys 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 do the rename work. There's nothing important/special/difficult to point on 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 informations 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 are more than one relation or one only (you can easily modify it if you want different renaming scheme).

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

Download Source Code

Content From : CodeProject