sql-server - 实现键、索引、约束的命名标准

Question

我有一个包含很多表的数据库，我想根据以下规则重命名主/外键、索引和默认约束:

• 主键:PK_<table name>
• 外键:FK_<table_name>_<column name1>_column name2>...
• 索引:IX_<table_name>_<column name1>_column name2>...
• 默认约束:DF_<table_name>_<column name>
• 检查约束:CK_<table_name>_<column name>

有人已经做过类似的 SQL 脚本了吗？

Answer 1

要将主键重命名为 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

要使用方案 FK_TableName_col_col_ReferencedName_col_col 重命名 FK:

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

对于外键，如果您只需要FK_TableName_ReferencedName，那么就简单得多:

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

对于索引，这将重命名所有索引IX_TableName_Col1_Col2...。它将忽略主键(因为它们在上面单独处理)，将添加UQ_到唯一索引/约束(因此IX_UQ_TableName_Col1_Col2...，将处理唯一约束和唯一索引相同，并且将忽略包含的列。(请注意，如果您有仅因包含的列而不同的冗余索引，则忽略包含的列可能会产生命名冲突。)

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

对于默认约束:

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

最后检查约束:

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

请注意，PRINT 不一定会显示整个语句，具体取决于文本结果的设置和语句的大小。但只要能观察到脚本是否正常工作就足够了。默认情况下，我将它们全部设置为 PrintOnly。