Implementing a naming standard for keys, indexes, constraints

Question

I have a database with a lot of tables, and I want to rename the primary/foreign keys, indexes and default constraints according to the following rules :

• Primary keys : PK_<table name>
• Foreign keys : FK_<table_name>_<column name1>_column name2>...
• Indexes : IX_<table_name>_<column name1>_column name2>...
• Default Constraints : DF_<table_name>_<column name>
• Check Constraints : CK_<table_name>_<column name>

Someone has already done a similar SQL script ?

Answer 1

And to rename the foreign keys, you can use something like this (this is not yet doing exactly what you wanted - but close enough to get started on it):

DECLARE RenameFKCursor CURSOR FAST_FORWARD
FOR 
    SELECT
       'dbo.sp_rename @objName = ''' + fk.Name + ''', @NewName = ''FK_' + t.Name + '_' + ref.Name + ''', @objtype = ''OBJECT'''
    FROM 
        sys.foreign_keys fk
    INNER JOIN 
        sys.tables t ON fk.parent_object_id = t.object_id
    INNER JOIN 
        sys.tables ref ON fk.referenced_object_id = ref.object_id
    WHERE
        fk.is_system_named = 1

DECLARE @RenameFKStmt NVARCHAR(500)

OPEN RenameFKCursor 

FETCH NEXT FROM RenameFKCursor INTO @RenameFKStmt

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        PRINT @RenameFKStmt
        EXEC(@RenameFKStmt)
    END

    FETCH NEXT FROM RenameFKCursor INTO @RenameFKStmt
END

CLOSE RenameFKCursor 
DEALLOCATE RenameFKCursor 
GO

Basically, you iterate over all foreign keys defined in your database, and you rename them to some name that you decide on how to construct in the SELECT that is the basis of this cursor.

Then you run the cursor over all results, and execute the dbo.sp_rename stored procedure to rename your FK constraint to whatever you want them to be.

Using Aaron's approach of just basically building up one single huge SQL statement, you could even get away without having to use a cursor.

This would be the very similar code to rename the "system-named" default constraints to your own naming convention - it uses the same approach as above, a SELECT against the system catalog views, and then a cursor to iterate over all entries and build up and execute a SQL rename statement:

DECLARE DFCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        dc.Name,
        t.Name,
        c.Name
    FROM 
        sys.default_constraints dc
    INNER JOIN  
        sys.tables t ON dc.parent_object_id = t.object_id
    INNER JOIN 
        sys.columns c ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id
    WHERE 
        is_system_named = 1

DECLARE @OldConstraintName sysname, @TableName sysname, @ColumnName sysname

OPEN DFCursor 

FETCH NEXT FROM DFCursor  INTO @OldConstraintName, @TableName, @ColumnName 

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Stmt NVARCHAR(999)

    SET @Stmt = 'dbo.sp_rename @objName = ''' + @OldConstraintName + ''', @NewName = ''DF_' + @TableName + '_' + @ColumnName + ''', @objtype = ''OBJECT'''

    PRINT @Stmt
    EXEC (@Stmt)

    FETCH NEXT FROM DFCursor INTO @OldConstraintName, @TableName, @ColumnName
END

CLOSE DFCursor 
DEALLOCATE DFCursor 

Answer 2

Provided solutions will break if DB has similar tables in different schemas. Here's my modification of this solution, that i use.

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 = 24;

Primary Keys:

IF @PrimaryKeys = 1
BEGIN
    SELECT @sql = @sql + @cr + @cr + N'/* ---- Primary Keys ---- */' + @cr;
    SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N'''
        + SCHEMA_NAME(schema_id) + '.'
        + 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

Foreign Keys:

IF @ForeignKeys = 1
BEGIN
    SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
    SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
        + SCHEMA_NAME(f.schema_id) + '.'
        + REPLACE(f.name, '''', '''''') + ''', @newname = N''FK_' 
        + LEFT(REPLACE(t.name, '''', ''), @TableLimit)
        + '_' + LEFT(REPLACE(t2.name, '''', ''), @TableLimit)           
        + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit)
        + ''';'
    FROM 
        sys.foreign_keys as f
        inner join  sys.foreign_key_columns as fk on f.object_id = fk.constraint_object_id
        inner join sys.tables as t on fk.parent_object_id = t.object_id         
        inner join sys.tables as t2 on fk.referenced_object_id = t2.object_id
        inner join sys.columns as c on fk.parent_object_id = c.object_id and 
                                        fk.parent_column_id = c.column_id 


    WHERE f.is_ms_shipped = 0;
END

Unique constraints:

 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

Default constraints:

IF @DefaultConstraints = 1
BEGIN
    SELECT @sql = @sql + @cr + @cr + N'/* ---- DefaultConstraints ---- */' + @cr;
    SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
        + SCHEMA_NAME(schema_id) + '.'
        + 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

Check Constraints:

IF @CheckConstraints = 1
BEGIN
    SELECT @sql = @sql + @cr + @cr + N'/* ---- CheckConstraints ---- */' + @cr;
    SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N'''
        + SCHEMA_NAME(schema_id) + '.' 
        + 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