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 Index Naming

By Michael Søndergaard,

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


The 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 point


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_
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 nonclustered index.
  • UCI = Unique clustered index.
  • NCI = Nonclustered index.
  • PK = Primary key constraint index.
  • UQ = Unique key constraint index.

However I got some issues with the result.

  1. If an index, doesn't have included columns, the name will have a superfluous "_" in the end.
  2. It is not easy to spot, where index columns and included columns begins and ends.
  3. 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.
  4. 4.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 = 1


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


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


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


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


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


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_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 begining 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 to the standard index convention.

EXECUTE dbautils.spFixIndexNaming
  @FilterExpression = 'Production.%',
  @IndexTypeFilter = 'I',
  @ReportMode = 1


Example 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 = 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: 605 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Covering Index using Included Columns

This article from Josef Richberg details the benefits of included columns for use in creating a cove...

FORUM

Included Columns

Why not include every table field in the included columns list for every index?

ARTICLE

Stairway to SQL Server Indexes: Level 5, Included Columns

Included columns enable nonclustered indexes to become covering indexes for a variety of queries, im...

FORUM

Indexes with Include

How does SQL select an index when you have included columns

FORUM

script included index

index include script

Tags
 
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