Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Michael Søndergaard,
The stored procedure dbautils.spFixForeignKeyNaming was written, because a lot of the databases I had inherited never had any consistency in their foreign key naming. Most of them had default names from the database table designer, some was named with almost random names, and others were based on some sort of naming convention, but were never followed through.
I like it, then the foreign key names, can help me to identify the tables and columns involved, without having to look at the meta data.
I accept that there are many different naming conventions, which people uses. That is why, I tried to create a very flexible way of enforcing naming conventions, and having the ability to support most of these different types of conventions.
The way to design a naming convention is with the use of the place holder tokens. Place holder tokens represent variables, which are substituted, with values from the database objects. Following place holder tokens are supported for foreign key naming conventions
I recommend using named parameters, instead of specifying the whole parameter list. I made all parameters optional, with default values. So it is actually possible to use, without specifying any parameters. Running the stored procedure is safe, because it will not change the database, it will only generating a report or rename statements. Only if the parameter @PerformUpdate >= 1 will it change the database directly.
I will demonstrate how to use the stored procedure by some examples, and by using the AdventureWorks database. If you want to try it yourself, you can download the AdventureWorks database from http://msftdbprodsamples.codeplex.com/Wikipage.
I will go through all of the parameters, available for this stored procedure. The parameters are also documented inside the header section of the stored procedure code. You could also look at the test cases for seeing their functionality. For easier usage I have also tried to align the functionality, so that parameters work the same way, across all my procedure.
Running the procedure as below, would generate a series of sp_rename statements, for renaming all foreign keys. It will only rename those, which doesn't comply with the default naming convention.
The procedure generates the following result (truncated, and selected samples picked out)
EXECUTE sp_rename '[Person].[Password].[FK_Password_Person_BusinessEntityID]','FK_Password_Person', 'object'EXECUTE sp_rename '[Person].[Person].[FK_Person_BusinessEntity_BusinessEntityID]','FK_Person_BusinessEntity', 'object'EXECUTE sp_rename '[Person].[PersonPhone].[FK_PersonPhone_Person_BusinessEntityID]','FK_PersonPhone_Person', 'object'
Let's say, I wanted to see, how all the foreign keys in the Adventure Works database, would look like, with my own custom naming convention. For this purpose the report mode is easier to read, so I need to set the parameter @ReportMode = 1.
I use my preferred naming convention for this example. It will tell me the name of the tables used for the foreign key, and also all the parent foreign key columns.
EXECUTE dbautils.spFixForeignKeyNaming @NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%', @UniquifyNames = 0, @ReportMode = 1
The execution of the procedure generates the following result (truncated and selected samples picked out)
Now it is easier to spot, which schema and tables are involved and a list of columns. In the databases I work with, the same table name can be used in different schemas. I also like it when it is easy to spot, there the referenced table and the column list begins.
The standard behavior for the stored procedure is to make sure, that no generated names are the same. This is done is by appending an incrementing number to the name, but only for duplicate names. This behavior was suppressed in one of the previous examples, with the use of the parameter @UniquifyNames = 0.
There are limitations to the stored procedure; it will only uniquify those foreign key names, which are returned as duplicates. It will not resolve name collisions with existing foreign key names, nor any dependency rename ordering, which may be present.
Let's see the uniquify functionality in action.
EXECUTE dbautils.spFixForeignKeyNaming @ReportMode = 1
Instead of the uniquifier functionality, it is also possible to fix the problem, by solving it manually. The only reason the problem exist in our example, was because we didn’t include the parent columns, another situation could be that same tables were used in different schema’s, the default convention doesn’t include schema, so it would also lead to duplicate names.
It is possible to limit the maximum length of a generated foreign key name, by setting a max length. All names that are larger than the specified @MaxNameLength parameter, or the SQL Server limit of 128 characters, will be truncated. Uniquifiers don't count towards the limit. Let's limit the names to a maximum of 40 characters.
EXECUTE dbautils.spFixForeignKeyNaming @NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%', @MaxNameLength = 40, @OversizedMode = 'T', @ReportMode = 1
The procedure generates the following result (truncated, and selected samples picked out).
This shows that foreign keys, had their new names truncated to a maximum of 40 characters.
Instead of truncating the foreign keys, setting the @OversizedMode = ‘S’, would have skipped the same foreign keys, and left them for manual renaming.
Truncating foreign key names at a fixed length, can leave some names cut off at inconvenient places. It is possible to set a limit for how many parent and referenced columns, which should be a part of the foreign key name. This is done by the parameters @MaxParentColumns and @MaxReferencedColumns.
EXECUTE dbautils.spFixForeignKeyNaming @NamingConvention= 'FK_%PARENT_TABLE_NAME%__%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%~%REFERENCED_COLUMNS%', @MaxParentColumns = 1, @MaxReferencedColumns = 1, @ReportMode = 1
The stored procedure can automatically replace schema, table, parent or referenced column name, with an object alias instead.
It works by using a mapping table and a synonym. The mapping table requires a certain structure, but may be named whatever you like. This is there the synonym comes in. The stored procedure requires the name to be called dbautils.AliasRulesSynonym, but it may point to a table in an external database. This is useful if you like to have a central object alias repository. The synonym give the flexibility, that you don't need to change the code of the stored procedure, you only need to change what the synonym points to.
The parameter @UseAliases is a char mask. By setting the correct char mask, you have total control of what types of aliasing you want to enable.
'C' = Column aliasing
'T' = Table aliasing
'S' = Schema aliasing
Shown below is an example of an alias rules table. Some of them may be a little artificially, but should make it easier to understand.
% mean a wildcard, the rule applies for all objects in that database area. It is only allowed to use either an exact object name, or %. Partial names like Prod% are not allowed.
More than one rule can apply for the same object, but the more specific rules, takes precedence over the more generic ones. A more thorough explanation of this, can be found in the documentation for the table value function dbautils.fnGetObjectAliases.
Please also note the importance of the empty table and column names, as they influence that type of alias rule it is.
% mean a wild-card, the rule applies for all objects in that database area. It is only allowed to use either an exact object name, or %. Partial names like Prod% are not allowed.
More than one rule can apply for the same object, but the more specific rules, takes precedence over the more generic ones. A more thorough explanation of this can be found in the documentation for the table value function dbautils.fnGetObjectAliases.
Please also note the importance's of the empty table and column names, as they influence that type of alias rule it is.
@UseAliases = 'STC',
@ReportMode = 1
The @UseAliases parameter may be combine with the other parameters @MaxNameLength, @MaxParentColumns, @MaxReferencedColumns and @OversizedMode. All of these options can help shortening the foreign key names.
Sometimes renaming all noncompliant foreign keys in one batch, can be a rather large change, so I have made it possible to easily filter a subset of tables. This is controlled by the parameter @FilterExpression, and is basically a like expression in disguise. The filter is applied on the parent schema and table
Here are some examples for the most common expressions and what they mean.
Here is an example of finding foreign key names in the Production schema, which doesn’t comply with the standard foreign convention.
EXECUTE dbautils.spFixForeignKeyNaming @FilterExpression = 'Production.%', @ReportMode = 1
In databases which are using a case insensitive collection, the procedure won’t report nor create rename statements for foreign keys, which complies to the naming convention, but differs in casing.
If you want your foreign keys to match in casing too, when you can force it, by setting the @ForceCaseSensitivity = 1. This will compare the old name with the new name by using the collation Latin1_General_BIN. If you want use another collation for case sensitive comparison; when you will have to adjust the stored procedure code. I haven’t found a better solution for accomplishing this, without the use of a case sensitive collation.
EXECUTE dbautils.spFixForeignKeyNaming @FilterExpression = 'Production.%', @ForceCaseSensitivity = 1, @ReportMode = 1
I have also created similar stored procedures for applying custom naming convention for column check constrains, foreign key constrains and indexes. These procedures will also be released to SQL Server Central. You can find the latest versions, updated documentation, test case scripts and other SQL goodies at sql.soendergaard.info
This script lists all foreign keys with parent and child table information
Trying to find parent table
A single column in a child table ref. multiple columns in a parent table
How to get schemaname, tablename, identity column, foreign key constraints
Find missing foreign keys in your database schema based on naming conventions.