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 Default Constraint Naming

By Michael Søndergaard,

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

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

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

Total article views: 291 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

Automate Default Constraints

One procedure that will take care of all default constraint values in the destination.

SCRIPT

Fix Default Constraint Naming

This stored procedure can be used for applying a custom default constraint naming

SCRIPT

Fix Column Check Constraint Naming

This stored procedure can be used for applying a custom column check constraint naming convention

FORUM

problem with naming default constraint and using it while adding a not null column

problem with naming default constraint and using it while adding a not null column

FORUM

Default Constraint Error

Getting an error while creating a default constraint dynamically

 
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