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

By:   |   Updated: 2012-06-19   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Database Administration


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-06-19

Comments For This Article




Thursday, March 5, 2020 - 7:21:41 PM - Vaidy A Back To Top (84962)

Beautiful.  Saved my day

Tuesday, December 17, 2019 - 12:02:26 PM - Pratap Kasthuri Back To Top (83452)

Thanks Aaron!

Monday, December 16, 2019 - 3:01:28 PM - Aaron Bertrand Back To Top (83441)

@pratap instead of a stored procedure you would just create a script using dynamic SQL that looks like the body of the procedure, and then run it on all DBs using a utility procedure like sp_ineachdb:

https://www.mssqltips.com/sqlservertip/5686/execute-a-command-in-the-context-of-each-database-in-sql-server-using-spineachdb/

(You could also create the procedure in each database that way. In either case, you'll have to deal with nested quotes when converting to dynamic SQL.)

Monday, December 16, 2019 - 11:02:36 AM - Pratap Kasthuri Back To Top (83437)

How can i loop through this sp on each user database instead of creating this sp on each database? 

Thursday, March 15, 2018 - 1:47:21 PM - George Keradinidis Back To Top (75434)

 Thank you for the great sp.

I have made the following alterations.

  1. For every section, checks to rename only if it is not the expected.
  2. The foreign keys are formed with columns name (like indexes)
  3. At the end select unchanged objects (not conforming to expected notations)

 

 

/*

exec dbo.ImplementNamingStandard 0,1,0,0,0,0 --PrimaryKeys       

exec dbo.ImplementNamingStandard 0,0,1,0,0,0 --ForeignKeys       

exec dbo.ImplementNamingStandard 0,0,0,1,0,0 --DefaultConstraints         

exec dbo.ImplementNamingStandard 0,0,0,0,1,0 --CheckConstraints

exec dbo.ImplementNamingStandard 0,0,0,0,0,1 --Indexes  

*/

if exists (select * from sysobjects where name like 'ImplementNamingStandard') drop PROCEDURE ImplementNamingStandard

GO

CREATE PROCEDURE ImplementNamingStandard 

    @SELECT_Only        BIT = 0,

    @PrimaryKeys        BIT = 0,

    @ForeignKeys        BIT = 0,

    @DefaultConstraints BIT = 0,

    @CheckConstraints   BIT = 0,

    @Indexes            BIT = 0

AS

BEGIN

    SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX)= N'', @cr CHAR(2)= CHAR(13) + CHAR(10);

DECLARE @TableLimit TINYINT= 40, @ColumnLimit TINYINT= 40;

 

    IF @PrimaryKeys = 1

        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

AND REPLACE(name, '''', '''''')!=N'PK_'+ LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit);

 

    IF @ForeignKeys = 1

        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 

            + REPLACE(name, '''', '''''') + ''', @newname = N''FK_' 

            + LEFT(REPLACE(OBJECT_NAME(f.parent_object_id), '''', ''), @TableLimit)

            + '_' + STUFF((

SELECT '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit)

                FROM sys.foreign_key_columns AS fc INNER JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id

                WHERE f.OBJECT_ID = fc.constraint_object_id

                ORDER BY constraint_column_id FOR XML PATH(''), 

                TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''';'

        FROM sys.foreign_keys f 

        WHERE f.is_ms_shipped = 0

AND REPLACE(name, '''', '''''')!='FK_' 

            + LEFT(REPLACE(OBJECT_NAME(f.parent_object_id), '''', ''), @TableLimit)

            + '_' + STUFF((

SELECT '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit)

                FROM sys.foreign_key_columns AS fc INNER JOIN sys.columns AS c ON fc.parent_object_id = c.object_id AND fc.parent_column_id = c.column_id

                WHERE f.OBJECT_ID = fc.constraint_object_id

                ORDER BY constraint_column_id FOR XML PATH(''), 

                TYPE).value('.', 'nvarchar(max)'), 1, 1, '');

 

    IF @DefaultConstraints = 1

        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

AND REPLACE(dc.name, '''', '''''')!='DF_' 

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

            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit);

 

    IF @CheckConstraints = 1

        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

AND REPLACE(cc.name, '''', '''''')!='CK_' 

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

            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit);

 

    IF @Indexes = 1

        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 

+ CASE is_unique_constraint WHEN 0 THEN (REPLACE(OBJECT_NAME(i.[object_id]), '''', '''''')) + '.' ELSE '' END

            + (REPLACE(i.name, '''', '''''')) + ''', @newname = N'''

+ CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_' END 

+ CASE is_unique WHEN 1 THEN 'U_'  ELSE '' 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

AND REPLACE(i.name, '''', '''''') != CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_' END 

+ CASE is_unique WHEN 1 THEN 'U_'  ELSE '' 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, '')

AND CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_' END 

+ CASE is_unique WHEN 1 THEN 'U_'  ELSE '' 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, '') NOT IN (SELECT NAME FROM sys.indexes)

 

    IF @SELECT_Only = 0 AND @sql > N'' EXEC sp_executesql @sql;

 

    select @sql [sql_text and/or unchanged] where @sql > N''

union all select name from sys.key_constraintswhere @primarykeys       =1 and name not like 'pk[_]%' and is_ms_shipped = 0 and type = 'pk' 

union all select name from sys.foreign_keyswhere @foreignkeys       =1 and name not like 'fk[_]%' and is_ms_shipped = 0 

union all select name from sys.default_constraintswhere @defaultconstraints=1 and name not like 'df[_]%' and is_ms_shipped = 0 

union all select name from sys.check_constraintswhere @checkconstraints  =1 and name not like 'ck[_]%' and is_ms_shipped = 0 

union all select name from sys.indexeswhere @indexes           =1 and name not like 'ix[_]%' and name not like 'uq[_]%' and objectproperty(object_id, 'ismsshipped') = 0 and index_id > 0 and is_primary_key = 0 and type in (1,2)  

 

END

GO

Thursday, March 8, 2018 - 10:27:37 AM - Miguel Back To Top (75376)

Really useful. Thanks

Had some problems when have several columns with FK to the same table.

The following code allows this format:

Change to FK_LOCALTABLE_LOCALFIELD_FKTABLE_FKFIELD;

 

    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N'''
            + REPLACE(name, '''', '''''') + ''', @newname = N''FK_'
            + LEFT(REPLACE(OBJECT_NAME(f.parent_object_id), '''', ''), @TableLimit)
            + '_' + LEFT(REPLACE(COL_NAME(fc.parent_object_id,fc.parent_column_id), '''', ''), @ColumnLimit) +
            + '_' + LEFT(REPLACE(OBJECT_NAME(f.referenced_object_id), '''', ''), @TableLimit) +
            + '_' + LEFT(REPLACE(COL_NAME(f.referenced_object_id,fc.referenced_column_id), '''', ''), @ColumnLimit)  +     ''';'
        FROM sys.foreign_keys f JOIN
             sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        WHERE f.is_ms_shipped = 0;
    END

Tuesday, February 6, 2018 - 8:22:45 AM - Martin Guth Back To Top (75112)

Fantastic script! You clearly outlined your assumptions and it works nice.

I have just found out in my scenario it works well to add a where condition on "name LIKE '%[0-9][A-F]%' ". This gets me just the system named constraints and indexes. Given the long sequence of hexadecimal values I guess it is quite unlikely to not have a combination of a digit and a character. I manually checked for the databases I applied the script to.

Martin

Thursday, November 30, 2017 - 2:01:25 PM - Aaron Bertrand Back To Top (73487)

 Calvin, yup, sorry about that. This code was written in 2012, before Azure SQL Database was really a thing.

Thursday, November 30, 2017 - 1:26:44 PM - Calvin Craig Back To Top (73482)

Many thanks for this useful SPROC!

I had to prefix the primary key name with [SchemaName].[TableName].

E.g., whereas your script generated:

EXEC sp_rename @objname = N'PK_BrakeRotor.BrakeRotors', @newname = N'PK_BrakeRotors';

which didn't work (on Azure SQL Database) and reported that no item of the name ... could be found.

 

Changing it to:

EXEC sp_rename @objname = N'[BrakeRotor].[BrakeRotors].[PK_BrakeRotor.BrakeRotors]', @newname = N'PK_BrakeRotors';

did the trick.

 

 

Wednesday, August 19, 2015 - 1:27:08 PM - Aaron Bertrand Back To Top (38485)

Sorry John, I tried, but I could not reproduce that problem (and I tried with multiple variations on the first column - no index, unique, pk non-clustered, etc). In all cases, index_column_id and key_ordinal match:

BatchStartTimeStamp	741577680	1	1	2	1
CommandStartTimeStamp	741577680	1	2	3	2
CommandEndTimeStamp	741577680	1	3	4	3
PointAverageID	741577680	1	4	5	4
AverageStartTimeStamp	741577680	1	5	6	5
AverageEndTimeStamp	741577680	1	6	7	6

Was this index changed at some point, and rebuilt/dropped/re-created since the change? Are there any other indexes on the table? What is @@VERSION? Do other indexes on this instance behave the same way? Can you reproduce on another instance?

Wednesday, August 19, 2015 - 12:26:32 PM - John Russell Back To Top (38484)

It's strange... It's definitely not an issue on all of the indexes in my client's database, but there are some cases where key_ordinal is more accurate. And for all of the other cases, key_ordinal is definitely not wrong.

If I have a multi column index such as:

CREATE UNIQUE CLUSTERED INDEX [ix_foo] ON [dbo].[foo]
(
   [BatchStartTimeStamp] ASC,
   [CommandStartTimeStamp] ASC,
   [CommandEndTimeStamp] ASC,
   [PointAverageID] ASC,
   [AverageStartTimeStamp] ASC,
   [AverageEndTimeStamp] ASC
)
GO

and run a query such as:

SELECT OBJECT_NAME(i.[object_id]) as table_name, i.name, (SELECT name from sys.columns as c where c.column_id = ic.column_id and c.object_id = ic.object_id) as column_name, ic.*
FROM
    sys.indexes as i
INNER JOIN
    sys.index_columns as ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id

WHERE OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0 and is_included_column = 0
ORDER BY OBJECT_NAME(i.[object_id]), i.name

i get results like:

table_name    name    column_name                    object_id    index_id    index_column_id    column_id    key_ordinal
foo                ix_foo    PointAverageID                 917578307    1                    1                        2               4
foo                ix_foo    BatchStartTimeStamp        917578307    1                    2                        3               1
foo                ix_foo    AverageStartTimeStamp    917578307    1                    3                        4               5
foo                ix_foo    AverageEndTimeStamp      917578307    1                    4                        5               6
foo                ix_foo    CommandStartTimeStamp  917578307    1                   5                         6               2
foo                ix_foo    CommandEndTimeStamp    917578307    1                   6                         7               3

key_ordinal better matches the order of my index script.

Really strange.

Wednesday, August 19, 2015 - 11:14:41 AM - Aaron Bertrand Back To Top (38482)

John, I haven't come across a case where there is any difference in those two columns, at least for key columns.

For include columns, there is an advantage to using index_column_id: while order of include columns doesn't matter to SQL Server, you can use this column to list those include columns in the same order they were originally defined (which can be important if you are generating schema scripts and need them to match, check for diffs, etc). For key_ordinal, all include columns are simply listed as 0.

Can you demonstrate a case where index_column_id is less accurate?

Wednesday, August 19, 2015 - 10:26:09 AM - John Russell Back To Top (38479)

Great post!! For the FK step, I'm curious why you chose to order by ic.index_column_id as opposed to ic.key_ordinal (where ic is sys.index_columns). The key_ordinal column seems to be more accurate in my limited testing. To be honest, I'm not really sure what index_column_id indicates.

Sunday, February 1, 2015 - 10:54:01 AM - Dave Clark Back To Top (36130)

To answer the question about drop/create rather than rename, I do believe that in general, that is the best option in most cases.

However, this article mentions in the "Problem" section some reasons why you would want to rename.

It mentions for example that you are implementing a new naming convention.

If you were to drop and create, you would have to do some unnecessary work.

For example, dropping and creating a default constraint would require you to additionally script the default value which would have to be either manually looked up or added to a script which generates the T-SQL.

For dropping and creating a primary key, the key column(s) would need to be determined for the create portion.

In the cases mentioned in this article, we are simply trying to change the name.  The other attributes remain the same, so sp_rename works great.

 

Wednesday, July 23, 2014 - 11:44:55 AM - B Back To Top (32841)

Is it better to drop the constraint and recreate it rather than renaming?

Tuesday, August 6, 2013 - 12:55:49 PM - Jose Back To Top (26120)

Really useful. Thanks!!


Monday, June 25, 2012 - 3:51:48 PM - Aaron Bertrand Back To Top (18193)

Dave, I had a preview mode - @SELECT_Only = 1

Monday, June 25, 2012 - 3:25:52 PM - Dave Millar-Haskell Back To Top (18192)

This is a useful bit of code. When I get time I'll incorporate Ludwig's code to exclude correct names and include a preview mode. I hate changing stuff without knowing exactly what will change.

Wednesday, June 20, 2012 - 4:49:57 PM - Aaron Bertrand Back To Top (18138)

Ludwig, you are assuming that everyone reading your code sample follows your definition of best practices. I don't think your ideas are sound for presenting code samples to an international audience. In any case, the data is stored as NVARCHAR, whether you want it to be or not, and procedures like sp_executesql and sp_rename require NVARCHAR parameters, so you should treat the strings as NVARCHAR. 

Wednesday, June 20, 2012 - 1:31:08 PM - Ludwig Guevara Back To Top (18135)

Aaron, it is not safe to use NVARCHAR for any of this, since objects, columns and other entities are stored as NVARCHAR (it doesn't matter, if SQL server allowed you to).

What if there is an object with a name that contains Unicode?
- I'm a DBA, Best practices: "Don't use unicode to name sql server objects", even if you use spanish, mandarin or cylindrical characters, even if you used them, no harm, no darn, right? We are allowed to display names before change them. I suggest the change to increase the size of @sql to display more objects and because I know my database.

Thanks.

Wednesday, June 20, 2012 - 10:38:07 AM - Aaron Bertrand Back To Top (18129)

Scott, the point of the article was not to tell you how to name your foreign keys. You'll notice I started with "let's assume you have *these* rules." The point of the article was to show how you could go about implementing those rules. If you change the rules, you'll obviously have to adjust the syntax to account for it, but it doesn't change the spirit or intent of the article. (Nor does making the code 15 character tidier, for that matter.)

Wednesday, June 20, 2012 - 10:35:06 AM - Felipe Maurer Back To Top (18127)

I created a script based on this one that finds duplicate indexes and FK's so you can drop unused ones. It takes into account the fields in the referencing table and the primary table so as not to delete useful FK's. Here it is:

 

IF OBJECT_ID('dbo.REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2') IS NOT NULL
    DROP VIEW dbo.
    REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2
GO

CREATE VIEW REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2
AS

SELECT KCU1.CONSTRAINT_CATALOG AS 'CONSTRAINT_CATALOG',
       KCU1.CONSTRAINT_SCHEMA AS 'CONSTRAINT_SCHEMA',
       KCU1.CONSTRAINT_NAME AS 'CONSTRAINT_NAME',
       KCU1.TABLE_CATALOG AS 'TABLE_CATALOG',
       KCU1.TABLE_SCHEMA AS 'TABLE_SCHEMA',
       KCU1.TABLE_NAME AS 'TABLE_NAME',
       KCU1.COLUMN_NAME AS 'COLUMN_NAME',
       KCU1.ORDINAL_POSITION AS 'ORDINAL_POSITION',
       KCU2.CONSTRAINT_CATALOG AS 'UNIQUE_CONSTRAINT_CATALOG',
       KCU2.CONSTRAINT_SCHEMA AS 'UNIQUE_CONSTRAINT_SCHEMA',
       KCU2.CONSTRAINT_NAME AS 'UNIQUE_CONSTRAINT_NAME',
       KCU2.TABLE_CATALOG AS 'UNIQUE_TABLE_CATALOG',
       KCU2.TABLE_SCHEMA AS 'UNIQUE_TABLE_SCHEMA',
       KCU2.TABLE_NAME AS 'UNIQUE_TABLE_NAME',
       KCU2.COLUMN_NAME AS 'UNIQUE_COLUMN_NAME'
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
            ON  KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
            AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
            AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
            ON  KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
            AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
            AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE  KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
GO

DECLARE @Objetos  TABLE(
            TipoObjeto VARCHAR(50),
            NombreObjeto VARCHAR(100),
            TablaBase VARCHAR(100),
            CamposBase VARCHAR(500),
            TablaReferenciada VARCHAR(100),
            CamposReferenciados VARCHAR(500),
            CamposIndexados VARCHAR(500)
        )

DECLARE @FK       TABLE(
            TablaBase VARCHAR(100),
            CamposBase VARCHAR(500),
            TablaReferenciada VARCHAR(100),
            CamposReferenciados VARCHAR(500),
            NumRegistros INT
        )

DECLARE @NDX      TABLE(
            TablaBase VARCHAR(100),
            CamposIndexados VARCHAR(500),
            NumRegistros INT
        )

INSERT INTO @Objetos
  (
    TipoObjeto,
    NombreObjeto,
    TablaBase,
    CamposBase,
    TablaReferenciada,
    CamposReferenciados
  )
SELECT DISTINCT 'FK',
       rcc.CONSTRAINT_NAME,
       rcc.TABLE_NAME,
       REPLACE(
           (
               SELECT rccu.COLUMN_NAME AS [data()]
               FROM   REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2 rccu
               WHERE  rcc.CONSTRAINT_NAME = rccu.CONSTRAINT_NAME
               ORDER BY
                      rccu.ORDINAL_POSITION
                      FOR XML PATH('')
           ),
           ' ',
           ','
       ),
       rcc.UNIQUE_TABLE_NAME,
       REPLACE(
           (
               SELECT rccu2.UNIQUE_COLUMN_NAME AS [data()]
               FROM   REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2 rccu2
               WHERE  rcc.CONSTRAINT_NAME = rccu2.CONSTRAINT_NAME
               ORDER BY
                      rccu2.ORDINAL_POSITION
                      FOR XML PATH('')
           ),
           ' ',
           ','
       ) --rcc.UNIQUE_COLUMN_NAME
FROM   REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2 rcc

INSERT INTO @Objetos
  (
    TipoObjeto,
    NombreObjeto,
    TablaBase,
    CamposIndexados
  )
SELECT CASE is_unique_constraint
            WHEN 1 THEN 'Unique Constraint'
            ELSE 'Index'
                 + CASE is_unique
                        WHEN 1 THEN ' Unique'
                        ELSE ''
                   END
       END + CASE has_filter
                  WHEN 1 THEN ' Filtered'
                  ELSE ''
             END,
       REPLACE(NAME, '''', ''''''),
       REPLACE(OBJECT_NAME(i.[object_id]), '''', ''''''),
       STUFF(
           (
               SELECT ', ' + REPLACE(c.name, '''', '')
               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

INSERT INTO @FK
  (
    TablaBase,
    CamposBase,
    TablaReferenciada,
    CamposReferenciados,
    NumRegistros
  )
SELECT TablaBase,
       CamposBase,
       TablaReferenciada,
       CamposReferenciados,
       COUNT(*)
FROM   @Objetos
WHERE  TipoObjeto = 'FK'
GROUP BY
       TablaBase,
       CamposBase,
       TablaReferenciada,
       CamposReferenciados
HAVING COUNT(*) > 1

SELECT o.TipoObjeto,
       o.NombreObjeto,
       o.TablaBase,
       o.CamposBase,
       o.TablaReferenciada,
       o.CamposReferenciados
FROM   @FK f
       JOIN @Objetos o
            ON  o.TablaBase = f.TablaBase
            AND o.TablaReferenciada = f.TablaReferenciada
            AND o.CamposBase = f.CamposBase
            AND o.CamposReferenciados = f.CamposReferenciados
            AND o.TipoObjeto = 'FK'
ORDER BY
       3,
       4,
       2

INSERT INTO @NDX
  (
    TablaBase,
    CamposIndexados,
    NumRegistros
  )
SELECT o.TablaBase,
       o.CamposIndexados,
       COUNT(*)
FROM   @Objetos o
WHERE  TipoObjeto <> 'FK'
GROUP BY
       o.TablaBase,
       o.CamposIndexados
HAVING COUNT(*) > 1

SELECT o.TipoObjeto,
       o.NombreObjeto,
       o.TablaBase,
       o.CamposIndexados
FROM   @NDX n
       JOIN @Objetos o
            ON  o.TablaBase = n.TablaBase
            AND o.CamposIndexados = n.CamposIndexados
            AND o.TipoObjeto <> 'FK'
ORDER BY
       3,
       4,
       2
GO

IF OBJECT_ID('dbo.REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2') IS NOT NULL
    DROP VIEW dbo.
    REFERENTIAL_CONSTRAINTS_COLUMN_USAGE2
GO

Wednesday, June 20, 2012 - 9:47:24 AM - Scott C Back To Top (18123)

I've been using SQL Server long enough that it seems like heresy to not name foreign keys "FK_<parent>_<reference>".  I could go along with adding the column name where there are multiple foreign keys between the same pair of tables, but not with dropping the name of the referenced table.

 WITH keys AS (
    SELECT  KeyName = fk.name,
            ParentID = fk.parent_object_id,
            ParentTable = OBJECT_NAME(fk.parent_object_id),
            ReferID = fk.referenced_object_id,
            ReferTable = OBJECT_NAME(fk.referenced_object_id),
            ReferColumn = c.name
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns kc ON fk.object_id = kc.constraint_object_id AND kc.constraint_column_id = 1
    INNER JOIN sys.columns c ON kc.parent_object_id = c.object_id AND kc.parent_column_id = c.column_id
    WHERE fk.is_ms_shipped = 0 ),
dupes AS (
    SELECT  ParentID, ReferID, UseColumnName = CASE COUNT(*) WHEN 1 THEN 0 ELSE 1 END
    FROM keys
    GROUP BY ParentID, ReferID )
SELECT  'EXEC sys.sp_rename N''' + KeyName + ''', N''' + ProposedName + ''', ''OBJECT'';'
FROM (
    SELECT  KeyName, 
         ProposedName = 'FK_' + ParentTable + '__' + ReferTable + CASE UseColumnName WHEN 1 THEN '__' + ReferColumn ELSE '' END
    FROM keys k
    INNER JOIN dupes d ON k.ParentID = d.ParentID AND k.ReferID = d.ReferID
) rn
WHERE rn.KeyName <> ProposedName AND ProposedName NOT IN (SELECT name FROM sys.all_objects)

Tuesday, June 19, 2012 - 9:28:03 PM - Aaron Bertrand Back To Top (18111)

Ludwig, it is not safe to use VARCHAR for any of this, since objects, columns and other entities are stored as NVARCHAR. What if there is an object with a name that contains Unicode? 

Tuesday, June 19, 2012 - 5:02:01 PM - Ludwig Guevara Back To Top (18109)

Hi,

Thanks for the script, I just want to suggest you some changes :

SQL Server 2008

DECLARE
--    @sql NVARCHAR(MAX) = N''
    @sql VARCHAR(MAX) = N'' -- Better use for larger Databases
    , @cr CHAR(2) = CHAR(13) + CHAR(10)
--    , @TableLimit TINYINT = 24
--   , @ColumnLimit TINYINT = 10

/* ---- Primary Keys ---- */

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_'
         -- It works better if, we change the @TableLimit for the length of the name of the parent_object_id
      + LEFT( REPLACE( OBJECT_NAME( parent_object_id ), '''', '' ), LEN( OBJECT_NAME( parent_object_id ) ) ) + ''';'
   FROM sys.key_constraints
   WHERE type = 'PK'
      AND is_ms_shipped = 0
      -- To avoid listing all the Primary Keys, who has already the SQL Server naming convention
      AND name NOT LIKE ('PK_' + LEFT( REPLACE( OBJECT_NAME( parent_object_id ), '''', '' ),
          LEN( OBJECT_NAME( parent_object_id ) ) ) )
END

NOTE:
The same condition has to be add it to the other SQL statements.

===================

If the Table has more than one FK, the script won't work.

EXEC sp_rename @objname = N'FK_PromotionSources_personId', @newname = N'FK_PromotionSources_Parties';
EXEC sp_rename @objname = N'FK_PromotionSources_organizationId', @newname = N'FK_PromotionSources_Parties';

Will work better if we used the same structure as is describe for "Unique Constrains and Indexes"

===================

I'm also changing the use of variable @ColumnLimit for LEN( c.name ), this will amplify the use in larger databases.

Thanks.

Tuesday, June 19, 2012 - 4:19:16 PM - Scott C Back To Top (18108)

This is an interesting and useful article.  I just had a minor quibble.

The subquery to concatenate index column names could be simplified a little.  The section 

'_' + STUFF((SELECT ... FOR XML PATH(''), TYPE).value(...), 1, 1, '')

can be simplified to

(SELECT ... FOR XML PATH(''))

The STUFF is required to delete the leading underscore only because there is an extra underscore in front of it, and .value(...) is required only because of the TYPE option.

I enjoyed the article and played with the code, otherwise I wouldn't have found little things to complain about.














get free sql tips
agree to terms