Script to rename constraints and indexes to conform to a SQL Server naming convention

Problem

In many systems, keys, indexes and constraints are given names generated by the system. These system-generated names relate somewhat to the objects they belong to, but often have some truncation of entity names as well as the addition of meaningless uniquifying strings. The result is that these names are often at odds with naming conventions that have been established, or need to be updated when you have decided to implement a new (or change an existing) naming convention. As you can imagine, going in and renaming every single key, index and constraint, one by one, is going to be a very tedious exercise.

Solution

In SQL Server 2005, there is a set of very clearly-defined metadata that will allow you to construct statements so that you can rename these entities to more sensible names that conform to your conventions. Let’s assume you have these rules for ideal names for various entities:

Primary keys:          PK_<TableName>

Foreign keys:          FK_<TableName>_<ReferencedTableName>

Unique constraints:    UQ_<TableName>_<column1>[…_<columnN>]

Indexes:               IX_<TableName>_<column1>[…_<columnN>]

Default constraints:   DF_<TableName>_<ColumnName>

Check constraints:     CK_<TableName>_<ColumnName>

For indexes specifically, we’ll make a few assumptions:

1. We’ll ignore primary keys (since they will be handled separately).
2. We’ll ignore columnstore indexes (I don’t know of a way to create a columnstore index with a system-generated name).
3. We’ll assume no redundant indexes exist (which would lead to collisions – sadly SQL Server allows this).
4. We’ll ignore INCLUDE columns (though this may lead to collisions if you have redundant indexes that are different only by the list of INCLUDE columns).
5. We’ll use “IX_U_” as the prefix for unique indexes (not unique constraints).
6. We’ll use “IX_F_” as the prefix for filtered indexes (and if the filtered index is also unique, then “IX_U_F_”).

Here is the procedure that I propose you could use to clean up the naming conventions for these entities in your database as described above:

CREATE PROCEDURE dbo.ImplementNamingStandard

    @SELECT_Only        BIT = 1,

    @PrimaryKeys        BIT = 1,

    @ForeignKeys        BIT = 1,

    @Indexes            BIT = 1,

    @UniqueConstraints  BIT = 1,

    @DefaultConstraints BIT = 1,

    @CheckConstraints   BIT = 1

AS

BEGIN

    SET NOCOUNT ON;




    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);

    SELECT @sql = N”, @cr = CHAR(13) + CHAR(10);




    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;

    SELECT @TableLimit = 24, @ColumnLimit = 10;




    IF @PrimaryKeys = 1

    BEGIN

        SELECT @sql = @sql + @cr + @cr + N’/* —- Primary Keys —- */’ + @cr;

        SELECT @sql = @sql + @cr + N’EXEC sp_rename @objname = N”’

            + REPLACE(name, ””, ”””) + ”’, @newname = N”PK_’

            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), ””, ”), @TableLimit) + ”’;’

        FROM sys.key_constraints

        WHERE type = ‘PK’

        AND is_ms_shipped = 0;

    END




    IF @ForeignKeys = 1

    BEGIN

        SELECT @sql = @sql + @cr + @cr + N’/* —- Foreign Keys —- */’ + @cr;

        SELECT @sql = @sql + @cr + N’EXEC sp_rename @objname = N”’

            + REPLACE(name, ””, ”””) + ”’, @newname = N”FK_’

            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), ””, ”), @TableLimit)

            + ‘_’ + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), ””, ”), @TableLimit) + ”’;’

        FROM sys.foreign_keys

        WHERE is_ms_shipped = 0;

    END




    IF (@UniqueConstraints = 1 OR @Indexes = 1)

    BEGIN

        SELECT @sql = @sql + @cr + @cr + N’/* —- Indexes / Unique Constraints —- */’ + @cr;

        SELECT @sql = @sql + @cr + N’EXEC sp_rename @objname = N”’

   + CASE is_unique_constraint WHEN 0 THEN

   QUOTENAME(REPLACE(OBJECT_NAME(i.[object_id]), ””, ”””)) + ‘.’ ELSE ” END

            + QUOTENAME(REPLACE(i.name, ””, ”””)) + ”’, @newname = N”’

            + CASE is_unique_constraint WHEN 1 THEN ‘UQ_’ ELSE ‘IX_’

              + CASE is_unique WHEN 1 THEN ‘U_’  ELSE ” END

            END + CASE has_filter WHEN 1 THEN ‘F_’  ELSE ” END

            + LEFT(REPLACE(OBJECT_NAME(i.[object_id]), ””, ”), @TableLimit)

            + ‘_’ + STUFF((SELECT ‘_’ + LEFT(REPLACE(c.name, ””, ”), @ColumnLimit)

                FROM sys.columns AS c

                    INNER JOIN sys.index_columns AS ic

                    ON ic.column_id = c.column_id

                    AND ic.[object_id] = c.[object_id]

                WHERE ic.[object_id] = i.[object_id]

                AND ic.index_id = i.index_id

                AND is_included_column = 0

                ORDER BY ic.index_column_id FOR XML PATH(”),

                TYPE).value(‘.’, ‘nvarchar(max)’), 1, 1, ”) +”’;’

        FROM sys.indexes AS i

        WHERE index_id > 0 AND is_primary_key = 0 AND type IN (1,2)

        AND OBJECTPROPERTY(i.[object_id], ‘IsMsShipped’) = 0;

    END




    IF @DefaultConstraints = 1

    BEGIN

        SELECT @sql = @sql + @cr + @cr + N’/* —- DefaultConstraints —- */’ + @cr;

        SELECT @sql = @sql + @cr + N’EXEC sp_rename @objname = N”’

            + REPLACE(dc.name, ””, ”””) + ”’, @newname = N”DF_’

            + LEFT(REPLACE(OBJECT_NAME(dc.parent_object_id), ””,”), @TableLimit)

            + ‘_’ + LEFT(REPLACE(c.name, ””, ”), @ColumnLimit) + ”’;’

        FROM sys.default_constraints AS dc

        INNER JOIN sys.columns AS c

        ON dc.parent_object_id = c.[object_id]

        AND dc.parent_column_id = c.column_id

        AND dc.is_ms_shipped = 0;

    END




    IF @CheckConstraints = 1

    BEGIN

        SELECT @sql = @sql + @cr + @cr + N’/* —- CheckConstraints —- */’ + @cr;

        SELECT @sql = @sql + @cr + N’EXEC sp_rename @objname = N”’

            + REPLACE(cc.name, ””, ”””) + ”’, @newname = N”CK_’

            + LEFT(REPLACE(OBJECT_NAME(cc.parent_object_id), ””,”), @TableLimit)

            + ‘_’ + LEFT(REPLACE(c.name, ””, ”), @ColumnLimit) + ”’;’

        FROM sys.check_constraints AS cc

        INNER JOIN sys.columns AS c

        ON cc.parent_object_id = c.[object_id]

        AND cc.parent_column_id = c.column_id

        AND cc.is_ms_shipped = 0;

    END




    SELECT @sql;




    IF @SELECT_Only = 0 AND @sql > N”

    BEGIN

        EXEC sp_executesql @sql;

    END

END

GO

As a quick example, let’s create a simple database with a couple of tables, chock full of system-defined names:

CREATE DATABASE testing;

GO

USE testing;

GO

CREATE TABLE dbo.table1

(

    keyCol INT,

    col2 INT CHECK (col2 IN (3,4)),

    col3 DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (keyCol)

);

GO

CREATE UNIQUE INDEX index1 ON dbo.table1(col2,keyCol) INCLUDE (col3);

CREATE INDEX index2 ON dbo.table1(col2) WHERE col3 >= ‘20000101’;

GO

CREATE TABLE dbo.table2

(

    keyCol INT PRIMARY KEY,

    otherKey INT FOREIGN KEY REFERENCES dbo.table1(keyCol),

    UNIQUE(otherKey,keyCol)

);

GO

In an ideal world, the above code would be created with explicit names for all keys and constraints, and with more meaningful names defined for the indexes. Using the stored procedure above, we can fix this. Create the procedure in this database, switch to “Results to Text” and execute the following:

EXEC dbo.ImplementNamingStandard @SELECT_Only = 1;

Results:

/* —- Primary Keys —- */




EXEC sp_rename @objname = N’PK__table1__90D23482EB4173F7′, @newname = N’PK_table1′;

EXEC sp_rename @objname = N’PK__table2__90D23482BDC29C35′, @newname = N’PK_table2′;




/* —- Foreign Keys —- */




EXEC sp_rename @objname = N’FK__table2__otherKey__15502E78′, @newname = N’FK_table2_table1′;




/* —- Indexes / Unique Constraints —- */




EXEC sp_rename @objname = N'[table1].[index1]’, @newname = N’IX_U_table1_col2_keyCol’;

EXEC sp_rename @objname = N'[table1].[index2]’, @newname = N’IX_F_table1_col2′;

EXEC sp_rename @objname = N’UQ__table2__EEE7556E798B5475′, @newname = N’UQ_table2_otherKey_keyCol’;




/* —- DefaultConstraints —- */




EXEC sp_rename @objname = N’DF__table1__col3__117F9D94′, @newname = N’DF_table1_col3′;




/* —- CheckConstraints —- */




EXEC sp_rename @objname = N’CK__table1__col2__108B795B’, @newname = N’CK_table1_col2′;

In this case we can see all of the results, but if your schema is large, it is unlikely you will be able to preview the whole script. This is more just to eyeball the script and make sure it is doing what you expect. If you want to preview line-by-line, you may wish to call the procedure multiple times, each time conditionally setting only one of the inclusion parameters to 1 and the rest to 0.

Some Caveats

1. Since in most cases generating the preferred name is relatively complex, we’ve avoided also checking if the preferred name is already in use (meaning it may rename an object that already meets your standard).
2. In addition, this solution won’t necessarily list the objects in the right order, so it is possible you could try to rename the PK for table A with an identical name already (incorrectly) in use for table B.
3. If you have long column names, you might not be able to apply the full new name. An identifier is defined using SYSNAME, so I have limited the output to 128 characters for all of the objects above. You could adjust the script to shorten column names to allow for them all to fit, e.g. by only outputting the first 10 characters for each column and the first 24 characters for each table name. These numbers were arbitrary selections, but you can change them easily at the beginning of the procedure.
4. There is no way to suppress all the “Caution: renaming an object may break…” status messages.
5. If you use index hints or explicit forceseek hints, you will want to tread lightly when renaming indexes. Depending on your architecture, it would be very tedious to find all of those references in your stored procedures, the plan cache, and application code in order to update them to use the new names.
6. The above scripts do not include schema as part of the naming scheme. You can potentially have duplicates if you have identical tables in different schema, so you may want to account for that using the OBJECT_SCHEMA_NAME() function. If you do include schema in the object names, be aware that there are edge cases where duplicate names can be produced. Imagine two schemas, foo and foo_bar, and two tables, foo.bar_x and foo_bar.x. Renaming the primary key for these tables using the scheme PK_schema_table would both yield PK_foo_bar_x.
7. Similarly, be aware that a column like “Document.Folder_Name” will yield the same “table_column” output as “Document_Folder.Name” – so if you use underscores in your entity names, you might choose a different character sequence to provide separation in your index and constraint names.
8. Most importantly, use the @SELECT_Only parameter to review the output before implementing the changes.

Conclusion

The procedure above should give you some ideas for correcting or improving the naming convention that is implemented in your database. By defaulting the @SELECT_Only parameter to 1, it is relatively harmless to install the stored procedure and see exactly what it would come up with for better names, or to adjust the rules for each type of entity to make it produce output more like your convention than my own.

Next Steps

• Create the stored procedure above in a local or test database.
• Run the procedure to see how your naming conventions could be quickly enforced.
• Review the following tips and other resources:
  • Tutorial : Naming conventions for SQL Server stored procedures
  • Use consistent SQL Server Naming Conventions
  • Enforce SQL Server Database Naming Conventions Using Policy Based Management
  • Use SQL Server Code Snippets to encourage consistent conventions