Fix Default Constraint Naming

I wrote the stored procedure dbautils.spFixColumnDefaultNaming, so that I could assign a more consistent naming standard, for all the column default 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 default 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 default constraint naming conventions

Placeholder token

Description

%SCHEMA_NAME%

Returns the schema name for the table, for which the column default constraint belongs too

%TABLE_NAME%

Returns the table name without a schema part, for which the column default constraint belongs too.

%COLUMN_NAME%

Returns the column name, for which the column default constraint are defined for

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 fromhttp://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.

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 default constraints. It will only rename those, which doesn't comply with the default naming convention.

EXECUTE dbautils.spFixColumnDefaultNaming

This generates the following message. "All tables matching the filter expression have default 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 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

The execution of the procedure generates the following result (truncated and selected samples picked out)

SchemaName

TableName

OldName

NewName

HumanResources

Employee

DF_Employee_VacationHours

DF_HumanResources_Employee_VacationHours

HumanResources

EmployeePayHistory

DF_EmployeePayHistory_ModifiedDate

DF_HumanResources_EmployeePayHistory_ModifiedDate

Production

BillOfMaterials

DF_BillOfMaterials_PerAssemblyQty

DF_Production_BillOfMaterials_PerAssemblyQty

Purchasing

ProductVendor

DF_ProductVendor_ModifiedDate

DF_Purchasing_ProductVendor_ModifiedDate

Purchasing

ShipMethod

DF_ShipMethod_ShipBase

DF_Purchasing_ShipMethod_ShipBase

Sales

SalesPerson

DF_SalesPerson_Bonus

DF_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 default constraint names, which are returned as duplicates. It will not resolve name collisions with existing default constraint names, nor any dependency rename ordering, which may be present.

The default naming convention for column default constraint won't generate duplicates, but it is possible if you have defined a convention, which doesn't include enough information for making it unique, e.g. failing to include %COLUMN_NAME% in the convention.

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.

It is possible to limit the maximum length of a generated column default 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.

The procedure generates the following result (truncated and selected samples picked out).

SchemaName

TableName

OldName

NewName

HumanResources

EmployeePayHistory

DF_EmployeePayHistory_ModifiedDate

DF_HumanResources_EmployeePayHistory_Mod

HumanResources

JobCandidate

DF_JobCandidate_ModifiedDate

DF_HumanResources_JobCandidate_ModifiedD

Person

StateProvince

DF_StateProvince_IsOnlyStateProvinceFlag

DF_Person_StateProvince_IsOnlyStateProvi

This table shows that the generated name have been truncated to a maximum of 40 characters.

Instead of truncating the column default 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.

The procedure generates the following result (truncated and selected samples picked out).

SchemaName

TableName

OldName

NewName

Production

BillOfMaterials

DF_BillOfMaterials_PerAssemblyQty

DF_Prod_BOM_PerAssemblyQty

Production

Product

DF_Product_MakeFlag

DF_Prod_Prod_MakeFlag

Purchasing

PurchaseOrderHeader

DF_PurchaseOrderHeader_SubTotal

DF_Purchasing_PurOrdHead_SubTtl

Sales

SalesOrderHeader

DF_SalesOrderHeader_SubTotal

DF_Sales_SalesOrderHeader_SubTtl

Sales

SalesTerritory

DF_SalesTerritory_SalesYTD

DF_Sales_SalesTerritory_SlsYTD

Sales

SalesPerson

DF_SalesPerson_SalesYTD

DF_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 default constraint names.

Example 7 - Renaming a subset of column default constraints

Sometimes renaming all noncompliant column default 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 default constraints in the Production schema.

In case insensitive databases the procedure won't report nor create rename statements for column default constraints, which are compliant to the naming convention, but differs in casing. If you want your column default 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.

Final stuff

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