Implementing a naming standard for keys, indexes, constraints

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 ?

Solution 1:

To rename Primary Keys to simply PK_TableName:

CREATE PROCEDURE dbo.Rename_PrimaryKeys
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' 
        + REPLACE(name, '''', '''''') + ''', ''PK_' 
        + REPLACE(OBJECT_NAME(parent_object_id), '''', '') + ''', ''OBJECT'';'
    FROM sys.key_constraints
    WHERE type = 'PK'
    AND name <> 'PK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '')
    AND OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

To rename FKs with the scheme FK_TableName_col_col_ReferencedName_col_col:

CREATE PROCEDURE dbo.Rename_ForeignKeys_WithColumns
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) 
    + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''')
        + ''', ''FK_' + REPLACE(OBJECT_NAME(fk.parent_object_id), '''', '') 
    + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '')
        FROM sys.columns AS c 
            INNER JOIN sys.foreign_key_columns AS fkc 
            ON fkc.parent_column_id = c.column_id
            AND fkc.parent_object_id = c.[object_id]
        WHERE fkc.constraint_object_id = fk.[object_id]
        ORDER BY fkc.constraint_column_id 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') 
    + '_' + REPLACE(OBJECT_NAME(fk.referenced_object_id), '''', '')
    + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '')
        FROM sys.columns AS c 
            INNER JOIN sys.foreign_key_columns AS fkc 
            ON fkc.referenced_column_id = c.column_id
            AND fkc.referenced_object_id = c.[object_id]
        WHERE fkc.constraint_object_id = fk.[object_id]
        ORDER BY fkc.constraint_column_id 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')  
        + ''', ''OBJECT'';'
    FROM sys.foreign_keys AS fk
    WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

For foreign keys if you just want FK_TableName_ReferencedName then it's a lot simpler:

CREATE PROCEDURE dbo.Rename_ForeignKeys
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' 
        + REPLACE(name, '''', '''''') + ''', ''FK_' 
        + REPLACE(OBJECT_NAME(parent_object_id), '''', '') 
        + '_' + REPLACE(OBJECT_NAME(referenced_object_id), '''', '')
        + ''', ''OBJECT'';'
    FROM sys.foreign_keys
    WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

For indexes, this will rename any indexes IX_TableName_Col1_Col2.... It will ignore primary keys (since they are dealt with separately above), will add UQ_ to unique indexes/constraints (so IX_UQ_TableName_Col1_Col2..., will treat unique constraints and unique indexes the same, and will ignore included columns. (Note that ignoring included columns could produce a naming conflict if you have redundant indexes that only differ by included columns.)

CREATE PROCEDURE dbo.Rename_Indexes
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) 
        + 'EXEC sp_rename ''' + REPLACE(i.name, '''', '''''')
        + ''', ''IX_' + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE '' END
        + 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(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')  
        +''', ''OBJECT'';'
    FROM sys.indexes AS i
    WHERE index_id > 0 
    AND is_primary_key = 0 -- dealt with separately
    AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

For default constraints:

CREATE PROCEDURE dbo.Rename_DefaultConstraints
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) 
        + 'EXEC sp_rename ''' + REPLACE(dc.name, '''', '''''') 
        + ''', ''DF_' + REPLACE(OBJECT_NAME(dc.parent_object_id), '''','') 
        + '_' + REPLACE(c.name, '''', '') + ''', ''OBJECT'';'
    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 OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

And finally check constraints:

CREATE PROCEDURE dbo.Rename_CheckConstraints
    @PrintOnly BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';

    SELECT @sql = @sql + CHAR(13) + CHAR(10) 
        + 'EXEC sp_rename ''' + REPLACE(cc.name, '''', '''''') 
        + ''', ''CK_' + REPLACE(OBJECT_NAME(cc.parent_object_id), '''','') 
        + '_' + REPLACE(c.name, '''', '') + ''', ''OBJECT'';'
    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 OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0;

    PRINT @sql;

    IF @PrintOnly = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO

Note that PRINT won't necessarily reveal the entire statement, depending on your settings for results in text and the size of the statement. But it should be good enough to eyeball that the scripts are doing the right job. I set them all to PrintOnly by default.

Solution 2:

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

Solution 3:

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

Implementing a naming standard for keys, indexes, constraints

Solution 1:

Solution 2:

Solution 3:

Related

Recent Posts