programing

SQL Server에서 외부 키 종속성을 찾는 방법은 무엇입니까?

abcjava 2023. 5. 6. 13:59
반응형

SQL Server에서 외부 키 종속성을 찾는 방법은 무엇입니까?

특정 열에 대한 모든 외부 키 종속성을 어떻게 찾을 수 있습니까?

다른 대안은 무엇입니까(그래픽으로 SSMS, SQL Server의 쿼리/뷰, 타사 데이터베이스 도구, .NET의 코드)?

다음 쿼리는 시작하는 데 도움이 됩니다.현재 데이터베이스 내의 모든 외부 키 관계를 나열합니다.

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

데이터베이스 다이어그램에서 SQL Server Management Studio 내의 관계를 그래픽으로 볼 수도 있습니다.

시도:sp_help [table_name]

모든 외래 키를 포함하여 테이블에 대한 모든 정보를 얻을 수 있습니다.

질문이 단일 테이블에 맞춰져 있으므로 다음을 사용할 수 있습니다.

EXEC sp_fkeys 'TableName'

SO에서 찾았습니다.

https://stackoverflow.com/a/12956348/652519

저는 제가 필요한 정보를 꽤 빨리 찾았습니다.외부 키의 테이블, 열 및 이름이 나열됩니다.

편집

다음은 사용할 수 있는 다양한 매개 변수를 자세히 설명하는 설명서 링크입니다. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

테이블 또는 열을 삭제하거나 이름을 변경하려는 경우 외부 키 종속성만 찾는 것만으로는 충분하지 않을 수 있습니다.

외부 키로 연결되지 않은 참조 테이블 - 외부 키로 연결되지 않은 참조 테이블도 검색해야 합니다(외부 키가 정의되지 않았지만 관련 데이터가 있는 잘못된 설계의 데이터베이스를 많이 보았습니다).솔루션은 모든 테이블에서 열 이름을 검색하고 유사한 열을 찾는 것일 수 있습니다.

다른 데이터베이스 개체 – 이 항목은 다소 주제와 다를 수 있지만 모든 참조를 찾는 경우 종속 개체를 확인하는 것도 중요합니다.

GUI 도구 – SSMS "관련 개체 찾기" 옵션 또는 ApexSQL Search(자유 도구, SSMS에 통합)와 같은 도구를 사용하여 외래 키로 연결된 테이블을 포함한 모든 종속 개체를 식별합니다.

저는 이 스크립트가 덜 비싸다고 생각합니다.

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

제가 정말 사용하고 싶은 것은 Red Gate Software의 SQL Dependency Tracker입니다.테이블, 저장 프로시저 등의 데이터베이스 개체를 넣으면 선택한 항목에 의존하는 다른 모든 개체 간의 관계 선이 자동으로 그려집니다.

스키마의 종속성을 그래픽으로 매우 잘 표현합니다.

존 샌섬 덕분에, 그의 질문은 훌륭합니다!

또한: " AND PT"를 추가해야 합니다.ORDERNAL_POSITION = CU.쿼리 끝에 "ORDINAL_POSITION"이 있습니다.

기본 키에 여러 필드가 있는 경우 이 문은 해당 필드를 서로 일치시킵니다(예: 쿼리에서 모든 조합을 생성했으므로 기본 키의 두 필드에 대해 해당 외부 키에 대해 4개의 결과를 얻었습니다).

(존의 답변에 대해 제가 평판 점수가 부족해서 코멘트를 드릴 수 없습니다.)

이 쿼리는 테이블의 외부 키에 대한 세부 정보를 반환하며 여러 열 키를 지원합니다.

    SELECT *
    FROM
    (
    SELECT 
    T1.constraint_name ConstraintName,
    T2.COLUMN_NAME ColumnName,
    T3.TABLE_NAME RefTableName, 
    T3.COLUMN_NAME RefColumnName,
    T1.MATCH_OPTION MatchOption, 
    T1.UPDATE_RULE UpdateRule, 
    T1.DELETE_RULE DeleteRule
    FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2 
    ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3 
    ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME 
    AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
    WHERE A.ConstraintName = 'table_name'

오랫동안 찾아본 끝에 저는 효과적인 해결책을 찾았습니다.내 데이터베이스는 sys를 사용하지 않습니다.foreign_key_messages 및 information_messages.key_column_messages에는 기본 키만 포함됩니다.

SQL Server 2015를 사용합니다.

솔루션 1(거의 사용되지 않음)

다른 솔루션이 작동하지 않는 경우 다음과 같이 하면 됩니다.

        WITH CTE AS
        (
            SELECT 
                TAB.schema_id,
                TAB.name,
                COL.name AS COLNAME,
                COl.is_identity
            FROM 
                sys.tables TAB INNER JOIN sys.columns COL 
                    ON TAB.object_id = COL.object_id
        )
        SELECT 
            DB_NAME() AS [Database], 
            SCHEMA_NAME(Child.schema_id) AS 'Schema',
            Child.name AS 'ChildTable',
            Child.COLNAME AS 'ChildColumn',
            Parent.name AS 'ParentTable',
            Parent.COLNAME AS 'ParentColumn'
        FROM 
            cte Child INNER JOIN CTE Parent
                ON 
                    Child.COLNAME=Parent.COLNAME AND 
                    Child.name<>Parent.name AND 
                    Child.is_identity+1=Parent.is_identity

솔루션 2(일반적으로 사용됨)

대부분의 경우 이 방법은 정상적으로 작동합니다.

        SELECT
            DB_NAME() AS [Database], 
            SCHEMA_NAME(fk.schema_id) AS 'Schema',
            fk.name 'Name',
            tp.name 'ParentTable',
            cp.name 'ParentColumn',
            cp.column_id,
            tr.name 'ChildTable',
            cr.name 'ChildColumn',
            cr.column_id
        FROM
            sys.foreign_keys fk
        INNER JOIN
            sys.tables tp ON fk.parent_object_id = tp.object_id
        INNER JOIN
            sys.tables tr ON fk.referenced_object_id = tr.object_id
        INNER JOIN
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
        INNER JOIN
            sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
        WHERE 
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%' 
        ORDER BY
            tp.name, cp.column_id

INFORMATION_SCHEMA를 사용할 수 있습니다.KEY_COLUM_USAGE 및 sys.테이블에 대한 외부 키 메타데이터(즉, 테이블에 대한 외부 키 메타데이터)를 가져오기 위해 foreign_key_metadata.제약 조건 이름, 참조 테이블 및 참조 열 등

다음은 질문입니다.

SELECT  CONSTRAINT_NAME, COLUMN_NAME, ParentTableName, RefTableName,RefColName FROM 
    (SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '<tableName>') constraint_details
    INNER JOIN  
    (SELECT ParentTableName, RefTableName,name ,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) RefColName  FROM (SELECT object_name(parent_object_id) ParentTableName,object_name(referenced_object_id) RefTableName,name,OBJECT_ID  FROM sys.foreign_keys WHERE parent_object_id = object_id('<tableName>') ) f 
    INNER JOIN   
    sys.foreign_key_columns AS fc  ON  f.OBJECT_ID = fc.constraint_object_id ) foreign_key_detail 
    on foreign_key_detail.name = constraint_details.CONSTRAINT_NAME

@"존 샌섬" 대답을 위한 메모입니다.

외부 키 종속성을 찾는다면 PT Where 절은 다음과 같아야 한다고 생각합니다.

i1.CONSTRAINT_TYPE = 'FOREIGN KEY'  -- instead of 'PRIMARY KEY'

ON 상태입니다.

ON PT.TABLE_NAME = FK.TABLE_NAME – instead of PK.TABLE_NAME

일반적으로 외국인 테이블의 기본 키를 사용하기 때문에 이 문제는 이전에는 주목받지 못했던 것 같습니다.

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

다음과 같은 이점이 있습니다.

FK가 속한 FK 자체 스키마

  • "참조 테이블" 또는 FK가 있는 테이블
  • "참조 열" 또는 FK를 가리키는 참조 테이블 내부의 열
  • "참조 테이블" 또는 FK가 가리키는 키 열이 있는 테이블
  • "참조된 열" 또는 FK가 가리키는 키인 열

USE information_schema;

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE (table_name = *tablename*) AND NOT (REFERENCED_TABLE_NAME IS NULL)

언급URL : https://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server

반응형

'programing' 카테고리의 다른 글

iOS 프로젝트를 빌드하지 못했습니다."xcodebuild" 명령을 실행했지만 오류 코드 65와 함께 종료되었습니다.  (0) 2023.05.06
딜러를 사용해야 하는 시기와 이유는 무엇입니까?  (0) 2023.05.06
StoryBoard ID란 무엇이며 어떻게 사용할 수 있습니까?  (0) 2023.05.06
NSURL을 로컬 파일 경로로 변환  (0) 2023.05.06
Bash에서 명령 출력에 변수를 설정하려면 어떻게 해야 합니까?  (0) 2023.05.06

'programing'의 다른글

  • 현재글SQL Server에서 외부 키 종속성을 찾는 방법은 무엇입니까?

관련글

댓글

티스토리툴바