spFixColumnCheckNaming
I wrote the stored procedure dbautils.spFixColumnCheckNaming, so that I could assign a more consistent naming standard, for all the column check constraint in my databases. The existing naming of the constraints was a mix of table designer naming, manual named and almost random named.
I have tried to create a very flexible stored procedure, which should give the user of the procedure, a lot of control in how the naming standard should be. I have done it, because I don’t believe in a right or wrong naming convention, I believe in a consistent naming convention instead.
Besides this page, more information about the procedure can be found in the file header for the stored procedure. It is recommended, that you read that also, as some information might be present only in the file.
Unlike index renaming, I don’t see any dangers in renaming column check constraints. In any case you should always test the renaming in a test or dev environment before applying it to a live environment.
The way to design a naming convention is with the use of the placeholder tokens. Placeholder tokens represent variables, which are substituted, with values from the database objects. The following placeholder tokens are supported for column check constraint naming conventions
| Placeholder token | Description |
|---|---|
| %SCHEMA_NAME% | Returns the schema name for the table, for which the column check constraint belongs too |
| %TABLE_NAME% | Returns the table name without a schema part, for which the column check constraint belongs too. |
| %COLUMN_NAME% | Returns the column name, for which the column check constraint are defined for |
| %ENABLED_STATE% | Returns the value corresponding to value in the @EnabledStates parameter. The value returned depends on, if the column check constraint are enabled or disabled |
| %REPLICATION_STATE% | Returns the value corresponding to value in the @ReplicationStates parameter. The value returned depends on, if the column check 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 column check constraint are trusted by sql server or not |
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 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 some 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.
Example 1 – Executing with default parameters only
Running the procedure as below would generate a series of sp_rename statements, for renaming all noncompliant column check constraints. It will only rename those, which doesn’t comply with the default naming convention.
EXECUTE dbautils.spFixColumnCheckNamingThis generates the following message. “All tables matching the filter expression have check constraints names, that complies with the specified naming convention…” Apparently the designers of the AdventureWorks database follow the default naming convention, specified in my stored procedure. That almost melts my heart.
Example 2 – Executing with custom naming convention
Let’s try an example where we overrule the default column check constraint naming convention
EXECUTE dbautils.spFixColumnCheckNaming
@NamingConvention = 'CK_%SCHEMA_NAME%_%TABLE_NAME%_%COLUMN_NAME%'The execution of the procedure generates the following result (truncated and selected samples picked out)
EXECUTE sp_rename '[HumanResources].[CK_EmployeePayHistory_Rate]','CK_HumanResources_EmployeePayHistory_Rate', 'object'
EXECUTE sp_rename '[Person].[CK_Person_EmailPromotion]','CK_Person_Person_EmailPromotion', 'object'
EXECUTE sp_rename '[Person].[CK_Person_PersonType]','CK_Person_Person_PersonType', 'object'
EXECUTE sp_rename '[Production].[CK_BillOfMaterials_PerAssemblyQty]','CK_Production_BillOfMaterials_PerAssemblyQty', 'object'
EXECUTE sp_rename '[Production].[CK_Document_Status]','CK_Production_Document_Status', 'object'
EXECUTE sp_rename '[Production].[CK_Location_Availability]','CK_Production_Location_Availability', 'object'Example 3 – Executing with report mode on
If we would like to see the result of an execution, and we don’t like reading sp_rename statements, we can turn the report mode on instead. This is done by setting the parameter @ReportMode = 1. The report mode makes the stored procedure output a table, showing the old and new values, based on the parameters
EXECUTE dbautils.spFixColumnCheckNaming
@NamingConvention = 'CK_%SCHEMA_NAME%_%TABLE_NAME%_%COLUMN_NAME%',
@ReportMode = 1
The execution of the procedure generates the following result (truncated and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| HumanResources | Employee | CK_Employee_VacationHours | CK_HumanResources_Employee_VacationHours |
| HumanResources | EmployeePayHistory | CK_EmployeePayHistory_PayFrequency | CK_HumanResources_EmployeePayHistory_PayFrequency |
| Production | BillOfMaterials | CK_BillOfMaterials_PerAssemblyQty | CK_Production_BillOfMaterials_PerAssemblyQty |
| Purchasing | ProductVendor | CK_ProductVendor_AverageLeadTime | CK_Purchasing_ProductVendor_AverageLeadTime |
| Purchasing | ShipMethod | CK_ShipMethod_ShipBase | CK_Purchasing_ShipMethod_ShipBase |
| Sales | SalesPerson | CK_SalesPerson_Bonus | CK_Sales_SalesPerson_Bonus |
Example 4 – 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 can be turned off with the use of the parameter @UniquifyNames = 0.
There are limitations to the stored procedure; it will only uniquify those check constraint names, which are returned as duplicates. It will not resolve name collisions with existing check constraint names, nor any dependency rename ordering, which may be present.
Normally the default naming convention for column check constraint won’t generate duplicates, but it is possible if you have defined multiple check constraints for the same column.
Let’s see the uniquify functionality in action. The example is a bit artificially, but the AdventureWorks database don’t have any examples, there a duplicate name will be generated
EXECUTE dbautils.spFixColumnCheckNaming
@NamingConvention = 'CK_%SCHEMA_NAME%_%TABLE_NAME%',
@ReportMode = 1
The execution of the procedure generates the following result (truncated and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Sales | SalesOrderHeader | CK_SalesOrderHeader_Freight | CK_Sales_SalesOrderHeader |
| Sales | SalesOrderHeader | CK_SalesOrderHeader_Status | CK_Sales_SalesOrderHeader2 |
| Sales | SalesOrderHeader | CK_SalesOrderHeader_SubTotal | CK_Sales_SalesOrderHeader3 |
| Sales | SalesOrderHeader | CK_SalesOrderHeader_TaxAmt | CK_Sales_SalesOrderHeader4 |
Example 5 – Solving long names with oversized mode – truncating & skipping.
It is possible to limit the maximum length of a generated column check constraint name; this is done 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.spFixColumnCheckNaming
@NamingConvention = 'CK_%SCHEMA_NAME%_%TABLE_NAME%_%COLUMN_NAME%',
@MaxNameLength = 40,
@OversizedMode = 'T',
@ReportMode = 1
The procedure generates the following result (truncated and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | WorkOrderRouting | CK_WorkOrderRouting_ActualCost | CK_Production_WorkOrderRouting_ActualCos |
| Production | WorkOrderRouting | CK_WorkOrderRouting_ActualResourceHrs | CK_Production_WorkOrderRouting_ActualRes |
| Production | WorkOrderRouting | CK_WorkOrderRouting_PlannedCost | CK_Production_WorkOrderRouting_PlannedCo |
This table shows that the generated name have been truncated to a maximum of 40 characters.
Instead of truncating the column check constraints setting the @OversizedMode = ‘S’, would have skipped the same constraints, and left them for manual renaming.
Example 6 – Solving long names with object aliases
The stored procedure can automatically replace schema, table and 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 synonym to be named 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 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 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.
| DatabaseName | SchemaName | TableName | ColumnName | AliasName | Alias Rule Type |
|---|---|---|---|---|---|
| % | Production | Prod | Schema alias rule | ||
| % | % | Product | Prod | Table alias rule | |
| AdventureWorks | % | BillOfMaterials | BOM | Table alias rule | |
| AdventureWorks | % | PurchaseOrderHeader | PurOrdHead | Table alias rule | |
| % | % | % | SalesYTD | SlsYTD | Column alias rule |
| % | % | % | SubTotal | SubTtl | Column alias rule |
EXECUTE dbautils.spFixColumnCheckNaming
@NamingConvention = 'CK_%SCHEMA_NAME%_%TABLE_NAME%_%COLUMN_NAME%',
@UseAliases = 'STC',
@ReportMode = 1The procedure generates the following result (truncated and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | BillOfMaterials | CK_BillOfMaterials_PerAssemblyQty | CK_Prod_BOM_PerAssemblyQty |
| Production | Product | CK_Product_ProductLine | CK_Prod_Prod_ProductLine |
| Purchasing | PurchaseOrderHeader | CK_PurchaseOrderHeader_SubTotal | CK_Purchasing_PurOrdHead_SubTtl |
| Sales | SalesOrderHeader | CK_SalesOrderHeader_SubTotal | CK_Sales_SalesOrderHeader_SubTtl |
| Sales | SalesTerritory | CK_SalesTerritory_SalesYTD | CK_Sales_SalesTerritory_SlsYTD |
| Sales | SalesPerson | CK_SalesPerson_SalesYTD | CK_Sales_SalesPerson_SlsYTD |
The @UseAliases parameter can be combined with the other parameters @MaxNameLength and @OversizedMode. All of these options can help shortening the column check constraint names.
Example 7 – Renaming a subset of column check constraints
Sometimes renaming all noncompliant column check constraints 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.
A more detailed coverage of the filter expression can be found in the documentation for the table value function dbautils.fnGetFilteredTables.
Here are some examples for the most common expressions and what they mean.
| Filter expression | Meaning |
|---|---|
| % | All tables in all schemas |
| %.% | All tables in all schemas |
| %.Product | All tables named product in all schemas |
| Production.% | All tables in schema Production |
| Prod%.% | All tables in schemas beginning with the name Prod |
| %,-Production.% | All tables in all schemas, except tables in the Production schema |
| Archive.%,-Archive.%199[0-9] | All tables in the Archive schema, except tables in schema Archive, there the table name ends with a year between 1990 and 1999 |
Here is an example of finding noncompliant column check constraints in the Production schema.
EXECUTE dbautils.spFixColumnCheckNaming
@FilterExpression = 'Production.%',
@ReportMode = 1Example 8 – Renaming column check constraints, with forced case sensitivity
In case insensitive databases the procedure won’t report nor create rename statements for column check constraints, which are compliant with the naming convention, but differs in casing. If you want your column check constraints 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.spFixColumnCheckNaming
@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_spFixColumnCheckNaming_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_spFixColumnCheckNaming_V2005-2008.sql
Søndergaard's SQL Site 