spFixIndexNaming
The stored procedure dbautils.spFixIndexNaming was written, because a lot of the databases I had inherited never had any consistency in their index naming. Most of them had default names from the database table designer, some was named with almost random names, and others were based on some sort of naming convention, but were never followed through.
I spent a lot of timing looking at query plans, so I like it, then index name can quickly tell me, that type of index it is. E.g. telling me if it is a primary key, unique key, clustered or non- clustered. The index columns are also great to include in the index name, but they have a tendency of increasing the name considerably.
I accept that there are many different naming conventions, which people uses. That is why, I tried to create a very flexible way of enforcing naming conventions, and having the ability to support most of these different types of conventions.
For indexes, I have found that we would probably need different conventions for primary keys, unique keys, standard indexes, XML and Spatial indexes. That is why I have created 5 different naming convention parameters for serving this purpose.
- @PrimaryKeyConvention
- @UniqueKeyConvention
- @IndexConvention
- @XmlIndexConvention
- @SpatialIndexConvention
With these parameters, it is possible to have different conventions, for the different types of indexes. The way to design a naming convention is with the use of the place holder tokens. Place holder tokens represent variables, which are substituted, with values from the database objects. Following place holder tokens are supported for index naming conventions
| Placeholder token | Description |
|---|---|
| %SCHEMA_NAME% | Returns the schema name for the table, for which the index belongs too |
| %TABLE_NAME% | Returns the table name without a schema part, for which the index belongs too. |
| %INDEX_COLUMNS% | Returns the list of columns which makes up the index, included columns are not part of this list. The separator used between index columns are determined by the @IndexColumnsSeparator |
| %INCLUDED_COLUMNS% | Returns the list of included columns in the index. The separator used between index columns are determined by the @IndexColumnsSeparator |
| %INDEX_COLUMNS_SEPARATOR% | Returns the @IndexColumnsSeparator value |
| %INDEX_COLUMNS_SECTION_SEPARATOR% | Returns the @IndexColumnsSectionSeparator value |
| %INCLUDED_COLUMNS_SECTION_SEPARATOR% | Returns either an empty string or the @IncludedColumnsSectionSeparator value, but only if included columns are present in the index |
| %INDEX_TYPE% | Returns the value corresponding to value in the @IndexTypes parameter. The value returned depends on, if the index is a clustered or non- clustered index |
| %UNIQUE_INDEX% | Returns the value corresponding to value in the @IndexProperties parameter. The value returned depends on, if the index is unique or not |
| %XML_INDEX_TYPE% | Returns the value corresponding to value in the @XmlIndexTypes parameter. The value returned depends on, if the index is a primary, secondary property, secondary value or secondary path xml index |
| %FILTERED_INDEX% | Returns the value corresponding in the @IndexProperties parameter. The value returned depends on, if the index are a filtered index, or not |
| %SPATIAL_INDEX_TYPE% | Returns the value corresponding in the @SpatialIndexTypes parameter. The value returned depends on, if the spatial index is a geometry or geographic index. Not supported in the 2005 stored procedure version |
| %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 |
| %ALLOW_ROWLOCK% | Returns the value corresponding to value in the @IndexProperties parameter. The value returned depends on, if the index allows row locks or not |
| %ALLOW_PAGELOCK% | Returns the value corresponding to value in the @IndexProperties parameter. The value returned depends on, if the index allows page locks or not |
| %PADDED_INDEX% | Returns the value corresponding in the @IndexProperties parameter. The value returned depends on, if the index is padded or not |
| %IGNORE_DUP_KEY% | Returns the value corresponding in the @IndexProperties parameter. The value returned depends on, if the index ignore duplicate keys, 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 now demonstrate, how to use the stored procedure by some examples, and by using the AdventureWorks database. If you want to try it you self, you can download the AdventureWorks database from http://msftdbprodsamples.codeplex.com/Wikipage.
I will go through most of the supported parameters, but some may be left out. Those are mostly self-explanatory, and are documented inside the header in the stored procedure code. You could also look at the test cases for seeing their functionality.
Example 1 – Executing with default parameters only
Running the procedure as below, would generate a series of sp_rename statements, for renaming all indexes. It will only rename those, which doesn’t comply with the default naming convention.
EXECUTE dbautils.spFixIndexNamingThe procedure generates the following result (truncated, and selected samples picked out)
EXECUTE sp_rename '[HumanResources].[Department].[AK_Department_Name]','IX_Department_Name', 'index'
EXECUTE sp_rename '[HumanResources].[Employee].[AK_Employee_LoginID]','IX_Employee_LoginID', 'index'
EXECUTE sp_rename '[HumanResources].[Employee].[AK_Employee_NationalIDNumber]','IX_Employee_NationalIDNumber', 'index'Example 2 – Custom naming convention for indexes, unique key constraints and primary keys only.
Let’s say, I wanted to see, how the indexes, unique and primary keys in the Adventure Works database, would look like, based on my custom naming convention, for this purpose the report mode is easier to read, so I need to set the parameter @ReportMode = 1.
Because the default is to rename all types of indexes, I will have to set the @IndexTypeFilter = ‘PUI’, so that the procedure only renames indexes, unique and primary keys. I use my preferred naming convention for this example. It will tell me the name of the table, it is part of, the type of index, all index columns and all included columns.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%_%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%_%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%_%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@ReportMode = 1,
@UniquifyNames = 0 -- only set for proving a pointThe execution of the procedure generates the following result (truncated, and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document_rowguid_ |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate_ |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType_Name_ |
| Person | Address | IX_Address_StateProvinceID | NCI_Address_StateProvinceID_ |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview_ProductID_ReviewerName_Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog_DatabaseLogID_ |
| Production | Product | PK_Product_ProductID | PKCI_Product_ProductID_ |
| Research | Product | PK_Product_ProductID | PKCI_Product_ProductID_ |
Now it is easier to spot, which indexes are clustered or non-clustered, and which of them are unique indexes. Here is how the index names should be read.
- UNCI = Unique non-clustered index.
- UCI = Unique clustered index.
- NCI = Non-clustered index.
- PK = Primary key constraint index.
- UQ = Unique key constraint index.
However I got some issues with the result.
- If an index, doesn’t have included columns, the name will have a superfluous “_” in the end.
- It is not easy to spot, where index columns and included columns begins and ends.
- If two tables are named the same, but exist in different schemas, this convention would make it hard to differentiate. No examples exist in AdventureWorks, so I had to invent an example. If we had a table called Research.Product, we would end up with the same index names.
- The index name can end up with names exceeding the limit set by SQL Server.
In the next couple of examples, I will try to solve the above issues. Some of the issues can be resolve by different means.
Example 3 – Solving superfluous included column separator, when no included columns exists.
The problem with the superfluous included column separator occurs, because it was hardcoded into the naming convention. The stored procedure has no way of knowing that it should be removed, if included columns don’t exist in the index. This is there the place holder token %INCLUDED_COLUMNS_SECTION_SEPARATOR% comes in. This placeholder will be replaced with the value of the parameter @IncludedColumnsSectionSeparator, but only if included columns exists in the index.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%_%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@ReportMode = 1 The execution of the procedure generates the following result (truncated, and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document_rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType_Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Address_StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview_ProductID_ReviewerName_Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog_DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Product_ProductID |
Example 4 – Read friendly separation of index columns and included columns
If we look at one of the previous generated index names, which also have included columns, it will become clear, that it is hard to spot, where the included columns begins. For example take a look at this index name NCI_ProductReview_ProductID_ReviewerName_Comments. It relative easy to see, that the first index column is ProductID, but it is impossible to see, that the included columns begins with Comments. In order to clarify the sections, we could use different tokens for each index column sections.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '-',
@ReportMode = 1The execution of the stored procedure generates the following result, making it easier to locate index and included columns sections.
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials~ProductAssemblyID_ComponentID_StartDate |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType~Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Address~StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview~ProductID_ReviewerName-Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog~DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Product~ProductID |
Example 5 – 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 was suppressed in one of the previous examples, with the use of the parameter @UniquifyNames = 0.
There are limitations to the stored procedure; it will only uniquify those index names, which are returned as duplicates. It will not resolve name collisions with existing index names, nor any dependency rename ordering, which may be present.
Let’s see the uniquify functionality in action.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '~',
@ReportMode = 1The procedure generates the following result (truncated, and selected samples picked out)
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials~ProductAssemblyID_ComponentID_StartDate |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType~Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Address~StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview~ProductID_ReviewerName~Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog~DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Product~ProductID |
| Research | Product | PK_Product_ProductID | PKCI_Product~ProductID2 |
Instead of the uniquifier functionality, it is also possible to fix the problem, by solving it manually. The only reason the problem exist in our example, was the same table name were used in multiple schemas. We could have fixed this easily, by adding the schema name to the naming convention as shown below.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%SCHEMA_NAME%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%SCHEMA_NAME%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%SCHEMA_NAME%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '~',
@ReportMode = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Production_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_Production_BillOfMaterials~ProductAssemblyID_ComponentID_StartDate |
| Person | ContactType | AK_ContactType_Name | UNCI_Person_ContactType~Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Person_Address~StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_Production_ProductReview~ProductID_ReviewerName~Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_dbo_DatabaseLog~DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Production_Product~ProductID |
| Research | Product | PK_Product_ProductID | PKCI_Research_Product~ProductID |
Example 6 – Solving long names with oversized mode – truncating & skipping
It is possible to limit the maximum length of a generated index name, 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.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '~',
@MaxNameLength = 40,
@OversizedMode = 'T',
@ReportMode = 1This shows that index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate and IX_ProductReview_ProductID_Name, had their new names truncated to a maximum of 40 characters.
Instead of truncating the indexes, setting the @OversizedMode = ‘S’, would have skipped the same indexes, and left them for manual renaming.
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials~ProductAssemblyID_Co |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType~Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Address~StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview~ProductID_ReviewerName |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog~DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Product~ProductID |
Example 7 – Solving long names by limiting the number index & include columns
Truncating index names at a fixed length, can leave some column name cut off, it is possible to set a limit for how many index and included columns, which should be a part of the index. This is done by the parameters @MaxIndexColumns and @MaxIncludedColumns. As their names implies, the first one is for index columns and the second one is for included columns.
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '~',
@MaxIndexColumns = 1,
@MaxIncludedColumns = 1,
@ReportMode = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BillOfMaterials~ProductAssemblyID |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType~Name |
| Person | Address | IX_Address_StateProvinceID | NCI_Address~StateProvinceID |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview~ProductID~Comments |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog~DatabaseLogID |
| Production | Product | PK_Product_ProductID | PKCI_Product~ProductID |
Example 8 – Solving long names with object aliases
The stored procedure can automatically replace schema, table, index and include 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 name to be called 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 char 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 wildcard, 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 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 | ||
| AdventureWorks | Research | Rsrch | Schema alias rule | ||
| % | % | Product | Prod | Table alias rule | |
| AdventureWorks | % | BillOfMaterials | BOM | Table alias rule | |
| % | % | % | Name | NM | Column alias rule |
| AdventureWorks | % | % | Name | Nam | Column alias rule |
| AdventureWorks | % | Product | Name | PNam | Column alias rule |
| AdventureWorks | Research | Product | Name | PNm | Column alias rule |
| AdventureWorks | Production | % | ProductId | PID | Column alias rule |
| AdventureWorks | Production | BillOfMaterials | ProductAssemblyID | ProAssID | Column alias rule |
| AdventureWorks | Production | BillOfMaterials | ComponentID | CompID | Column alias rule |
| % | % | % | StartDate | StDate | Column alias rule |
| % | Person | % | StateProvinceID | StateProvId | Column alias rule |
| % | % | % | DatabaseLogID | DBLogId | Column alias rule |
| % | % | % | Comments | Cmnts | Column alias rule |
| % | % | % | ReviewerName | RevNam | Column alias rule |
EXECUTE dbautils.spFixIndexNaming
@PrimaryKeyConvention = 'PK%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@UniqueKeyConvention = 'UQ%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexConvention = '%UNIQUE_INDEX%%INDEX_TYPE%_%TABLE_NAME%%INDEX_COLUMNS_SECTION_SEPARATOR%%INDEX_COLUMNS%%INCLUDED_COLUMNS_SECTION_SEPARATOR%%INCLUDED_COLUMNS%',
@IndexTypeFilter = 'PUI',
@IndexColumnsSectionSeparator = '~',
@IncludedColumnsSectionSeparator = '~',
@UseAliases = 'STC',
@ReportMode = 1The procedure generates the following result (truncated, and selected samples picked out).
| SchemaName | TableName | OldName | NewName |
|---|---|---|---|
| Production | Document | UQ__Document__F73921F730F848ED | UQNCI_Document~rowguid |
| Production | BillOfMaterials | AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | UCI_BOM~ProAssID_CompID_StDate |
| Person | ContactType | AK_ContactType_Name | UNCI_ContactType~Nam |
| Person | Address | IX_Address_StateProvinceID | NCI_Address~StateProvId |
| Production | ProductReview | IX_ProductReview_ProductID_Name | NCI_ProductReview~PID_RevNam~Cmnts |
| dbo | DatabaseLog | PK_DatabaseLog_DatabaseLogID | PKNCI_DatabaseLog~DBLogId |
| Production | Product | PK_Product_ProductID | PKCI_Prod~PID |
The @UseAliases parameter may be combine with the other parameters @MaxNameLength, @MaxIndexColumns, @MaxIncludedColumns and @OversizedMode. All of these options can help shortening the index names.
Example 9 – Renaming subset of indexes
Sometimes renaming all noncompliant indexes 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.
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 index names in the Production schema, which doesn’t comply with the standard index convention.
EXECUTE dbautils.spFixIndexNaming
@FilterExpression = 'Production.%',
@IndexTypeFilter = 'I',
@ReportMode = 1Example 10 – Renaming indexes, with forced case sensitivity
In databases which are using a case insensitive collection, the procedure won’t report nor create rename statements for indexes, which complies with the naming convention, but differs in casing. If you want your indexes 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.spFixIndexNaming
@FilterExpression = 'Production.%',
@IndexTypeFilter = 'I',
@ForceCaseSensitivity = 1,
@ReportMode = 1Download section
This stored procedure come in multiple versions, so I have created two different install scripts. The scripts should be executed it in the database, where you want to apply an index naming convention. All created objects will be installed in the schema dbautils
SQL 2005 Install_spFixIndexNaming_V2005.sql
SQL 2008+Install_spFixIndexNaming_V2008.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_spFixIndexNaming_V2005-2008.sql
Søndergaard's SQL Site 