primary key, foreign key, default constraint, index 이름을 명명 규칙에 맞게 일괄 수정

레거시 DB에 명명 규칙을 적용하느라 스크립트 하나 만들어 봤습니다. (SQL 2005 / 2008)

스크립트에 적용한 명명 규칙은 아래와 같습니다.

- primary key : PK_테이블이름
- foreign key : FK_부모테이블이름_자식테이블이름_자식컬럼1_자식컬럼2_ ...
- default constraint : DF_테이블이름_컬럼이름
- index
    non unique & non clustered : IX_NN_테이블이름_컬럼이름1_컬럼이름2_ ...
    non unique & clustered : IX_NC_테이블이름_컬럼이름1_컬럼이름2_ ...
    unique & non clustered : IX_UN_테이블이름_컬럼이름1_컬럼이름2_ ...
    unique & clustered : IX_UC_테이블이름_컬럼이름1_컬럼이름2_ ...

명명 규칙이 다르다면 스크립트를 수정해서 사용~

rename_objects.sql

SET NOCOUNT ON;

GO

 

CREATE TABLE #Rename (

  seq int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

  objFullName sysname NOT NULL,

  objName sysname NOT NULL,

  [newName] sysname NOT NULL,

  objType sysname NOT NULL

);

GO

 

DECLARE @i int, @j int, @nvcObjName sysname, @nvcNewName sysname, @nvcStmt nvarchar(max);

 

WITH C1 (objFullName, objName, [newName], objType) AS (

  SELECT [name], [name], N'PK_' + OBJECT_NAME(parent_object_id), N'OBJECT' -- PRIMARY KEY

  FROM sys.key_constraints

  WHERE [type] = 'PK'

  UNION ALL

  SELECT FK.name, FK.name, N'FK_' + OBJECT_NAME(FK.referenced_object_id) + N'_' + OBJECT_NAME(FK.parent_object_id) + N'_' + S.columnNames, N'OBJECT' -- FOREIGN KEY

  FROM sys.foreign_keys FK

    INNER JOIN (

      SELECT FKC2.constraint_object_id, STUFF((

        SELECT N'_' + COL.name

        FROM sys.foreign_key_columns FKC

          INNER JOIN sys.columns COL ON FKC.parent_object_id = COL.[object_id] AND FKC.parent_column_id = COL.column_id

        WHERE FKC.constraint_object_id = FKC2.constraint_object_id

        ORDER BY COL.column_id

        FOR XML PATH('')

      ), 1, 1, N'') AS columnNames

      FROM sys.foreign_key_columns FKC2

      GROUP BY FKC2.constraint_object_id

    ) S ON FK.[object_id] = S.constraint_object_id

  UNION ALL

  SELECT DFC.name, DFC.name, N'DF_' + OBJECT_NAME(DFC.parent_object_id) + N'_' + COL.name, N'OBJECT' -- DEFAULT CONSTRAINT

  FROM sys.default_constraints DFC

    INNER JOIN sys.columns COL ON DFC.parent_object_id = COL.[object_id] AND DFC.parent_column_id = COL.column_id

  UNION ALL

  SELECT N'dbo.' + OBJECT_NAME(IDX.[object_id]) + N'.' + IDX.name, IDX.name

    , N'IX_' + CASE IDX.is_unique WHEN 1 THEN N'U' ELSE N'N' END + CASE IDX.[type] WHEN 1 THEN N'C' ELSE N'N' END + N'_' + OBJECT_NAME(IDX.[object_id]) + N'_' + S.columnNames

    , N'INDEX' -- INDEX

  FROM sys.indexes IDX

    INNER JOIN sys.tables TBL ON IDX.[object_id] = TBL.[object_id]

    INNER JOIN (

      SELECT IXC2.[object_id], IXC2.index_id, STUFF((

        SELECT N'_' + COL.name

        FROM sys.index_columns IXC

          INNER JOIN sys.columns COL ON IXC.[object_id] = COL.[object_id] AND IXC.column_id = COL.column_id

        WHERE IXC.[object_id] = IXC2.[object_id] AND IXC.index_id = IXC2.index_id

        ORDER BY IXC.key_ordinal

        FOR XML PATH('')

      ), 1, 1, N'') AS columnNames

      FROM sys.index_columns IXC2

      GROUP BY IXC2.[object_id], IXC2.index_id

    ) S ON IDX.[object_id] = S.[object_id] AND IDX.index_id = S.index_id

  WHERE IDX.is_primary_key = 0 AND IDX.[type] > 0

)

INSERT #Rename (objFullName, objName, [newName], objType)

SELECT objFullName, objName, [newName], objType

FROM C1

WHERE C1.objName <> C1.[newName];

 

SELECT @i = 1, @j = @@ROWCOUNT;

 

IF @j = 0

  PRINT N'이름규칙을위배한개체가없습니다.';

 

WHILE @i <= @j

BEGIN

  SELECT @nvcStmt = N'

IF EXISTS (SELECT * FROM sys.sql_modules WHERE definition LIKE N''%' + objName + N'%'')

BEGIN

  PRINT N''' + objName + N'을(를) 참조하는sql module이존재합니다.'';

  RETURN;

END

' +

    CASE objType

      WHEN N'OBJECT' THEN N'

IF OBJECT_ID(N''' + [newName] + N''') IS NOT NULL'

      WHEN N'INDEX' THEN N'

IF EXISTS (SELECT * FROM sys.indexes WHERE [name] = N''' + [newName] + N''')'

    END + N'

  PRINT N''' + objName + N'의새이름' + [newName] + '이(가) 이미존재합니다.'';

ELSE

  EXEC sp_rename N''' + objFullName + N''', N''' + [newName] + N''', N''' + objType + N''';

'

  FROM #Rename

  WHERE seq = @i;

 

  PRINT @nvcStmt + N'GO';

--  EXEC (@nvcStmt);

 

  SET @i = @i + 1;

END

 

DROP TABLE #Rename;

GO