mssql FOREIGN KEY 제약 조건에서 참조하므로 삭제할 수 없습니다.

제목과 같은 오류가 발생하면서

fk제약 조건때문에 테이블 재생성이 안된다..

누가(어떤 테이블) 참조하는지 알아야 할때..

PK,FK 전체를 OBJECTS 테이블에서 조회한다.

SELECT OBJECT_NAME(object_id)        AS CONSTRAINT_NAME
     , SCHEMA_NAME(schema_id)        AS SCHEMA_NAME
     , OBJECT_NAME(parent_object_id) AS TABLE_NAME
     , type_desc AS CONSTRAINT_TYPE
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')

PK View에서 pk조회

SELECT OBJECT_NAME(object_id)        AS CONSTRAINT_NAME
     , SCHEMA_NAME(schema_id)        AS SCHEMA_NAME
     , OBJECT_NAME(parent_object_id) AS TABLE_NAME FROM sys.key_constraints

FK View에서 fk조회

SELECT OBJECT_NAME(object_id)        AS CONSTRAINT_NAME
     , SCHEMA_NAME(schema_id)        AS SCHEMA_NAME
     , OBJECT_NAME(parent_object_id) AS TABLE_NAME FROM sys.foreign_keys

특정테이블을 참조하는 FK

SELECT
   f.name AS FOREIGN_KEY_NAME,
   OBJECT_NAME(f.parent_object_id) TABLE_NAME,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) COL_NAME
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE
   OBJECT_NAME (f.referenced_object_id) = '테이블이름'

또는 다음과 같이

SELECT fk.name       AS FOREIGN_KEY_NAME
     , t_parent.name AS PARENT_TABLE_NAME
     , c_parent.name AS PARENT_COLUMN_NAME
     , t_child.name  AS REFERENCED_TABLE_NAME
     , c_child.name  AS REFERENCED_COLUMN_NAME
FROM sys.foreign_keys fk
         INNER JOIN sys.foreign_key_columns fkc
                    ON fkc.constraint_object_id = fk.object_id
         INNER JOIN sys.tables t_parent
                    ON t_parent.object_id = fk.parent_object_id
         INNER JOIN sys.columns c_parent
                    ON fkc.parent_column_id = c_parent.column_id
                        AND c_parent.object_id = t_parent.object_id
         INNER JOIN sys.tables t_child
                    ON t_child.object_id = fk.referenced_object_id
         INNER JOIN sys.columns c_child
                    ON c_child.object_id = t_child.object_id
                        AND fkc.referenced_column_id = c_child.column_id
WHERE t_child.name='테이블이름'
ORDER BY t_parent.name, c_parent.name;

테이블명은 REBUILD하려는 테이블

참조를 삭제하고 테이블 재생성 후 다시 참조를 생성해 주면 된다.