Søndergaard's SQL Site

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

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 2 – Executing with custom naming convention

Let’s try an example where we overrule the default column default constraint naming convention

EXECUTE dbautils.spFixColumnDefaultNaming
  @NamingConvention = 'DF_%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].[DF_Employee_VacationHours]','DF_HumanResources_Employee_VacationHours', 'object'
EXECUTE sp_rename '[HumanResources].[DF_EmployeeDepartmentHistory_ModifiedDate]','DF_HumanResources_EmployeeDepartmentHistory_ModifiedDate', 'object'
EXECUTE sp_rename '[HumanResources].[DF_EmployeePayHistory_ModifiedDate]','DF_HumanResources_EmployeePayHistory_ModifiedDate', 'object'
EXECUTE sp_rename '[HumanResources].[DF_JobCandidate_ModifiedDate]','DF_HumanResources_JobCandidate_ModifiedDate', 'object'
EXECUTE sp_rename '[HumanResources].[DF_Shift_ModifiedDate]','DF_HumanResources_Shift_ModifiedDate', 'object'
EXECUTE sp_rename '[Person].[DF_Address_ModifiedDate]','DF_Person_Address_ModifiedDate', 'object'
EXECUTE sp_rename '[Person].[DF_Address_rowguid]','DF_Person_Address_rowguid', '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.spFixColumnDefaultNaming
  @NamingConvention = 'DF_%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	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

EXECUTE dbautils.spFixColumnDefaultNaming
  @NamingConvention = 'DF_%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	SpecialOffer	DF_SpecialOffer_DiscountPct	DF_Sales_SpecialOffer
Sales	SpecialOffer	DF_SpecialOffer_MinQty	DF_Sales_SpecialOffer2
Sales	SpecialOffer	DF_SpecialOffer_rowguid	DF_Sales_SpecialOffer3
Sales	SpecialOffer	DF_SpecialOffer_ModifiedDate	DF_Sales_SpecialOffer4

Example 5 – Solving long names with oversized mode – truncating & skipping.

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.

EXECUTE dbautils.spFixColumnDefaultNaming
  @NamingConvention = 'DF_%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
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.

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.spFixColumnDefaultNaming
  @NamingConvention = 'DF_%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	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
Arhive.%,-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.

EXECUTE dbautils.spFixColumnDefaultNaming
  @FilterExpression = 'Production.%',
  @ReportMode = 1

Example 8 – Renaming column default constraints, with forced case sensitivity

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.

EXECUTE dbautils.spFixColumnDefaultNaming
  @FilterExpression = 'Production.%',
  @ForceCaseSensitivity = 1,
  @ReportMode = 1

Download 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 default constraint naming convention. All created objects will be installed in the schema dbautils

SQL 2005+Install_spFixColumnDefaultNaming_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_spFixColumnDefaultNaming_V2005-2008.sql