Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

Fix Column Check Constraint Naming

By Michael Søndergaard,

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.spFixColumnCheckNaming


This 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 = 1


The 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 = 1


Example 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 = 1

Final stuff

I have also created similar stored procedures for applying custom naming convention for column check constrains, foreign key constrains and default constraints. 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

Total article views: 492 | Views in the last 30 days: 10
 
Related Articles
SCRIPT

Conversion of rule objects to column check constraints

This script can convert the usage of bound rule objects in tables to column check constraints

FORUM

Constraint Execution Order

Constraint Execution Order

ARTICLE

Using Check Constraints

Check constraints can be added to a table to enforce specific rules on one or more columns. This art...

FORUM

Check Constraint with Case Statement

Check Constraint with Case Statement

FORUM

Creating a Check Constraint

Check Constraint

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones