spFixForeignKeyNaming
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
| Placeholder token | Description |
|---|---|
| %PARENT_SCHEMA_NAME% | Returns the schema name for the parent table, for which the foreign key belongs to. |
| %PARENT_TABLE_NAME% | Returns the table name without a schema part for the parent table, for which the foreign key belongs to. |
| %PARENT_COLUMNS% | Returns a list of parent columns for the foreign key constraint. The separator used between foreign key columns are determined by the @ColumnsSeparator |
| %REFERENCED_SCHEMA_NAME% | Returns the schema name for the referenced table, for which the foreign key points to |
| %REFERENCED_TABLE_NAME% | Returns the table name without a schema part for the referenced table, for which the foreign key points to |
| %REFERENCED_COLUMNS% | Returns a list of referenced columns for the foreign key constraint. The separator used between foreign key columns are determined by the @ColumnsSeparator |
| %COLUMNS_SEPARATOR% | Returns the @ColumnsSeparator value |
| %ENABLED_STATE% | Returns the value corresponding to value in the @EnabledStates parameter. The value returned depends on, if the foreign key constraint are enabled or disabled |
| %REPLICATION_STATE% | Returns the value corresponding to value in the @ReplicationStates parameter. The value returned depends on, if the foreign key constraint are enforce in replication scenarios |
| %TRUSTED_STATE% | Returns the value corresponding to value in the @TrustedStates parameter. The value returned depends on, if the foreign key constraint are trusted by sql server or not |
| %DEL_REF_ACTION% | Returns the value corresponding to value in the @DeleteRefStates parameter. The value returned depends on, the foreign key constraint delete referential action |
| %UPD_REF_ACTION% | Returns the value corresponding to value in the @UpdateRefStates parameter. The value returned depends on, the foreign key constraint update referential action |
How to use the stored procedure
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 now demonstrate how to use the stored procedure by some examples, and by using the AdventureWorks database. If you want to try it you self, you can download the AdventureWorks database from http://msftdbprodsamples.codeplex.com/Wikipage.
I will go through most of the supported parameters, but some may be left out. Those are mostly self-explanatory, and are documented inside the header in the stored procedure code. You could also look at the test cases for seeing their functionality.
Example 1 – Executing with default parameters only
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.
EXECUTE dbautils.spFixForeignKeyNamingThe 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'
Example 2 – Custom naming convention for foreign keys.
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 = 1The execution of the procedure generates the following result (truncated, and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Sales | Customer | FK_Customer_Person_PersonID | FK_Sales_Customer__Person_Person~PersonID |
| Sales | Customer | FK_Customer_Store_StoreID | FK_Sales_Customer__Sales_Store~StoreID |
| Production | WorkOrder | FK_WorkOrder_Product_ProductID | FK_Production_WorkOrder__Production_Product~ProductID |
| Production | ProductReview | FK_ProductReview_Product_ProductID | FK_Production_ProductReview__Production_Product~ProductID |
| Purchasing | Vendor | FK_Vendor_BusinessEntity_BusinessEntityID | FK_Purchasing_Vendor__Person_BusinessEntity~BusinessEntityID |
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.
Example 3 – Solving duplicate generated names.
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 = 1The procedure generates the following result (truncated, and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | BillOfMaterials | FK_BillOfMaterials_Product_ProductAssemblyID | FK_Production_BillOfMaterials__Production_Product |
| Production | BillOfMaterials | FK_BillOfMaterials_Product_ComponentID | FK_Production_BillOfMaterials__Production_Product2 |
| Production | Product | FK_Product_UnitMeasure_SizeUnitMeasureCode | FK_Production_Product__Production_UnitMeasure |
| Production | Product | FK_Product_UnitMeasure_WeightUnitMeasureCode | FK_Production_Product__Production_UnitMeasure2 |
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.
Example 4 – Solving long names with oversized mode – truncating & skipping
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 = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Purchasing | PurchaseOrderDetail | FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID | FK_PurchaseOrderDetail_PurchaseOrderHead |
| Sales | SalesOrderHeaderSalesReason | FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID | FK_SalesOrderHeaderSalesReason_SalesOrde |
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.
Example 5 – Solving long names by limiting the number parent or referenced columns
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 = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Sales | SalesOrderDetail | FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID | FK_SalesOrderDetail__SpecialOfferProduct~SpecialOfferID~SpecialOfferID |
Example 6 – Solving long names with object aliases
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.
| DatabaseName | SchemaName | TableName | ColumnName | AliasName | Alias Rule Type |
|---|---|---|---|---|---|
| % | Production | Prod | Schema alias rule | ||
| AdventureWorks | Research | Rsrch | Schema alias rule | ||
| % | % | Product | Prod | Table alias rule | |
| AdventureWorks | % | BillOfMaterials | BOM | Table alias rule | |
| % | % | % | Name | NM | Column alias rule |
| AdventureWorks | % | % | Name | Nam | Column alias rule |
| AdventureWorks | % | Product | Name | PNam | Column alias rule |
| AdventureWorks | Research | Product | Name | PNm | Column alias rule |
| AdventureWorks | Production | % | ProductId | PID | Column alias rule |
| AdventureWorks | Production | BillOfMaterials | ProductAssemblyID | ProAssID | Column alias rule |
| AdventureWorks | Production | BillOfMaterials | ComponentID | CompID | Column alias rule |
| % | % | % | StartDate | StDate | Column alias rule |
| % | Person | % | StateProvinceID | StateProvId | Column alias rule |
| % | % | % | DatabaseLogID | DBLogId | Column alias rule |
| % | % | % | Comments | Cmnts | Column alias rule |
| % | % | % | ReviewerName | RevNam | Column alias rule |
EXECUTE dbautils.spFixForeignKeyNaming
@NamingConvention= 'FK_%PARENT_SCHEMA_NAME%_%PARENT_TABLE_NAME%__%REFERENCED_SCHEMA_NAME%_%REFERENCED_TABLE_NAME%~%PARENT_COLUMNS%~%REFERENCED_COLUMNS%',
@UseAliases = 'STC',
@ReportMode = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Product | FK_Product_ProductModel_ProductModelID | FK_Prod_Prod__Prod_ProductModel~ProductModelID~ProductModelID |
| Production | BillOfMaterials | FK_BillOfMaterials_Product_ComponentID | FK_Prod_BOM__Prod_Prod~CompID~CompID |
| Purchasing | PurchaseOrderDetail | FK_PurchaseOrderDetail_Product_ProductID | FK_Purchasing_PurchaseOrderDetail__Prod_Prod~ProductID~ProductID |
| Production | TransactionHistory | FK_TransactionHistory_Product_ProductID | FK_Prod_TransactionHistory__Prod_Prod~PID~PID |
| Production | BillOfMaterials | FK_BillOfMaterials_Product_ProductAssemblyID | FK_Prod_BOM__Prod_Prod~ProAssID~ProAssID |
| Production | WorkOrder | FK_WorkOrder_Product_ProductID | FK_Prod_WorkOrder__Prod_Prod~PID~PID |
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.
Example 7 – Renaming subset of foreign keys
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.
| Filter expression | Meaning |
|---|---|
| % | All foreign keys for all tables in all schemas |
| %.% | All foreign keys for all tables in all schemas |
| %.Product | All foreign keys with parent tables named product in all schemas |
| Production.% | All foreign keys with parent tables in schema Production |
| Prod%.% | All foreign keys with parent tables in schemas beginning with the name Prod |
| %,-Production.% | All foreign keys with parent tables in all schemas, except parent tables in the Production schema |
| Archive.%,-Archive.%199[0-9] | All foreign keys with parent tables in the Archive schema, except parent tables in schema Archive, there the table name ends with a year between 1990 and 1999 |
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 = 1Example 8 – Renaming foreign keys with forced case sensitivity
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 = 1Download section
At the moment this stored procedure comes in only one version. This version supports SQL Server 2005 and newer. I have created an install script, which will install the stored procedure and all required objects. You should execute it in the database, where you want to apply a column check constraint naming convention. All created objects will be installed in the schema dbautils
SQL 2005+ Install_spFixForeignKeyNaming_V2005-2008.sql
I have also created a test case script for ensuring the best possible backward compatibility, I used the test cases for development and as part of my build script process. The test case script are designed to be executed in tempdb, this ensures that no extra permissions are needed. The scripts will create a lot of temporary objects, for setting up test cases. It also tries to clean up after itself, but if for some reason it doesn’t, it will always be clean up after a SQL Server Service restart.
TestCasesOnly_spFixForeignKeyNaming_V2005-2008.sql
Søndergaard's SQL Site 