복붙노트

[SQL] SQL 서버 : 어떻게 INFORMATION_SCHEMA에서 외래 키 참조를 얻으려면?

SQL

SQL 서버 : 어떻게 INFORMATION_SCHEMA에서 외래 키 참조를 얻으려면?

SQL Server에서 어떻게 외래 키에서 참조 된 테이블 + 열 이름을 얻을 수 있나요?

참고 : 키가있는 테이블 / 열하지만 참조하는 키를 누릅니다.

예:

경우 테이블의 키 [FA_MDT_ID] T_ALV_Ref_FilterDisplay]. [T_AP_Ref_Customer]을 의미한다. MDT_ID]

이와 같은 제약 조건을 만들 때와 같은 :

ALTER TABLE [dbo].[T_ALV_Ref_FilterDisplay]  WITH CHECK ADD  CONSTRAINT [FK_T_ALV_Ref_FilterDisplay_T_AP_Ref_Customer] FOREIGN KEY([FA_MDT_ID])
REFERENCES [dbo].[T_AP_Ref_Customer] ([MDT_ID])
GO

나는 [T_AP_Ref_Customer]을 얻을 필요가있다. [MDT_ID] 주어 T_ALV_Ref_FilterAnzeige]. FA_MDT_ID] 입력으로서

해결법

  1. ==============================

    1.신경 쓰지 마, 이것은 정답은 : http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

    신경 쓰지 마, 이것은 정답은 : http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

    SELECT 
     KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA 
    ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

    노트 : INFORMATION_SCHEMA는 (은 독특한-제약 조건을 찾아 않음) 인덱스를 포함하지 않습니다. 그래서 당신은 고유의-인덱스를 기반으로 외국 키를 찾으려면, 당신은 마이크로 소프트 독점 테이블을 통해 이동해야합니다 :

    SELECT  
     fksch.name AS FK_CONSTRAINT_SCHEMA 
    ,fk.name AS FK_CONSTRAINT_NAME 

    ,sch1.name AS FK_TABLE_SCHEMA 
    ,t1.name AS FK_TABLE_NAME 
    ,c1.name AS FK_COLUMN_NAME 
    -- The column_id is not the ordinal, it can be dropped and then there's a gap... 
    ,COLUMNPROPERTY(c1.object_id, c1.name, 'ordinal') AS FK_ORDINAL_POSITION 

    ,COALESCE(pksch.name,sch2.name) AS REFERENCED_CONSTRAINT_SCHEMA 
    ,COALESCE(pk.name, sysi.name) AS REFERENCED_CONSTRAINT_NAME 

    ,sch2.name AS REFERENCED_TABLE_SCHEMA 
    ,t2.name AS REFERENCED_TABLE_NAME 
    ,c2.name AS REFERENCED_COLUMN_NAME 
    ,COLUMNPROPERTY(c2.object_id, c2.name, 'ordinal') AS REFERENCED_ORDINAL_POSITION 
FROM sys.foreign_keys AS fk 

LEFT JOIN sys.schemas AS fksch 
    ON fksch.schema_id = fk.schema_id 

-- not inner join: unique indices 
LEFT JOIN sys.key_constraints AS pk
    ON pk.parent_object_id = fk.referenced_object_id 
    AND pk.unique_index_id = fk.key_index_id 

LEFT JOIN sys.schemas AS pksch 
    ON pksch.schema_id = pk.schema_id 

LEFT JOIN sys.indexes AS sysi 
    ON sysi.object_id = fk.referenced_object_id 
    AND sysi.index_id = fk.key_index_id 

INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id 

INNER JOIN sys.tables AS t1 
    ON t1.object_id = fkc.parent_object_id 

INNER JOIN sys.schemas AS sch1 
    ON sch1.schema_id = t1.schema_id 

INNER JOIN sys.columns AS c1 
    ON c1.column_id = fkc.parent_column_id 
    AND c1.object_id = fkc.parent_object_id 

INNER JOIN sys.tables AS t2 
    ON t2.object_id = fkc.referenced_object_id 

INNER JOIN sys.schemas AS sch2 
    ON sch2.schema_id = t2.schema_id 

INNER JOIN sys.columns AS c2 
    ON c2.column_id = fkc.referenced_column_id 
    AND c2.object_id = fkc.referenced_object_id

    에지 경우에 대한 증명 시험 :

    CREATE TABLE __groups ( grp_id int, grp_name varchar(50), grp_name2 varchar(50) )
ALTER TABLE __groups ADD CONSTRAINT UQ___groups_grp_name2 UNIQUE (grp_name2)
CREATE UNIQUE INDEX IX___groups_grp_name ON __groups(grp_name)

GO
CREATE TABLE __group_mappings( map_id int, map_grp_name varchar(50), map_grp_name2 varchar(50), map_usr_name varchar(50) )
GO

ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups FOREIGN KEY(map_grp_name)
REFERENCES __groups (grp_name)
GO


ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups2 FOREIGN KEY(map_grp_name2)
REFERENCES __groups (grp_name2)
GO


SELECT @@VERSION -- Microsoft SQL Server 2016 (SP1-GDR) (KB4458842)
SELECT version() -- PostgreSQL 9.6.6 on x86_64-pc-linux-gnu
GO
  2. ==============================

    2.당신은 SQL Server 관련 스키마 카탈로그 뷰를 사용하여 살 수 있다면,이 쿼리는 당신이 찾고있는 무엇을 반환합니다 :

    당신은 SQL Server 관련 스키마 카탈로그 뷰를 사용하여 살 수 있다면,이 쿼리는 당신이 찾고있는 무엇을 반환합니다 :

    SELECT  
    fk.name,
    OBJECT_NAME(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    OBJECT_NAME(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column'
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
    sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id

    방법을 잘 모르 - 모두 한 경우 - 당신은 INFORMATION_SCHEMA 뷰에서 동일한 정보를 얻을 수 있습니다 ....

  3. ==============================

    3.나는 나에게 모든 "키"및 제약 누락 가진 "ID"열 / 찾도록 것이라고 버전을 원했다. 그래서 나는 여기에 내 쿼리입니다, 모든 PK 또는 FK는 null의 목록과 비교하여 모든 열을 원했다. 그것은 다른 사람을 도움이되기를 바랍니다!

    나는 나에게 모든 "키"및 제약 누락 가진 "ID"열 / 찾도록 것이라고 버전을 원했다. 그래서 나는 여기에 내 쿼리입니다, 모든 PK 또는 FK는 null의 목록과 비교하여 모든 열을 원했다. 그것은 다른 사람을 도움이되기를 바랍니다!

    SELECT 
     c.table_schema
    ,c.table_name
    ,c.column_name
    ,KeyConstraints.constraint_type
    ,KeyConstraints.constraint_schema
    ,KeyConstraints.constraint_name
    ,KeyConstraints.referenced_table_schema
    ,KeyConstraints.referenced_table_name
    ,KeyConstraints.referenced_column_name
    ,KeyConstraints.update_rule
    ,KeyConstraints.delete_rule
FROM information_schema.columns AS c 
LEFT JOIN 
    (
        SELECT 
             FK.table_schema AS TABLE_SCHEMA
            ,FK.table_name
            ,CU.column_name
            ,FK.constraint_type
            ,c.constraint_schema
            ,C.constraint_name
            ,PK.table_schema AS REFERENCED_TABLE_SCHEMA
            ,PK.table_name AS REFERENCED_TABLE_NAME
            ,CCU.column_name AS REFERENCED_COLUMN_NAME
            ,C.update_rule
            ,C.delete_rule
        FROM information_schema.referential_constraints AS C 

        INNER JOIN information_schema.table_constraints AS FK 
            ON C.constraint_name = FK.constraint_name 

        INNER JOIN information_schema.table_constraints AS PK 
            ON C.unique_constraint_name = PK.constraint_name 

        INNER JOIN information_schema.key_column_usage AS CU 
            ON C.constraint_name = CU.constraint_name 

        INNER JOIN information_schema.constraint_column_usage AS CCU 
            ON PK.constraint_name = CCU.constraint_name 

        WHERE ( FK.constraint_type = 'FOREIGN KEY' ) 

        UNION 

        SELECT 
             ccu.table_schema
            ,ccu.table_name
            ,ccu.column_name
            ,tc.constraint_type
            ,ccu.constraint_schema
            ,ccu.constraint_name
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
        FROM information_schema.constraint_column_usage ccu 

        INNER JOIN information_schema.table_constraints tc 
            ON ccu.table_schema = tc.table_schema 
            AND ccu.table_name = tc.table_name 

        WHERE tc.constraint_type = 'PRIMARY KEY'

    ) AS KeyConstraints 
    ON c.table_schema = KeyConstraints.table_schema 
    AND c.table_name = KeyConstraints.table_name 
    AND c.column_name = KeyConstraints.column_name 

WHERE c.column_name LIKE '%ID' OR c.column_name LIKE '%Key' 
ORDER BY  c.table_schema 
         ,c.table_name 
         ,c.column_name 
;

    http://www.dpriver.com/pp/sqlformat.htm : 사의 씨의 서식

  4. ==============================

    4.

    you can use the following script in order to find all the fk,pk relationship for specific table 

    *DECLARE @tablename VARCHAR(100)
    SET @tablename='xxxxxxx'
    Select 'Referenced by FK table' AS Type,  FK.TABLE_SCHEMA, FK.TABLE_NAME AS 
    'FK_TABLE_NAME' ,cu.COLUMN_NAME AS 'FK_ReferencingColumn',PK.TABLE_NAME AS 
    'PK_TABLE_NAME',
   ku.COLUMN_NAME AS 'PK_ReferencedColumn'
    From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC
        Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As PK
            On PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK
            On FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
       ON cu.CONSTRAINT_NAME = Rc.CONSTRAINT_NAME
         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
    ON ku.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
    Where 
        PK.TABLE_NAME = @tablename
    UNION  
    SELECT 'Referencing PK table' AS Type, FK.TABLE_SCHEMA, FK.TABLE_NAME AS 
    'FK_TABLE_NAME' ,cu.COLUMN_NAME AS 'FK_ReferencingColumn',PK.TABLE_NAME AS 
    'PK_TABLE_NAME',
     ku.COLUMN_NAME AS 'PK_ReferencedColumn'
    From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC
        Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As PK
            On PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK
            On FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
       ON cu.CONSTRAINT_NAME = Rc.CONSTRAINT_NAME
         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
    ON ku.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
   Where 
        fk.TABLE_NAME = @tablename*

    5. from https://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] C #에서 저장 프로 시저에서 반환 값을 얻기  (0) 2020.04.04
[SQL] 사용 MySQL의 공간적 확장은 원 내부의 점을 선택합니다  (0) 2020.04.04
[SQL] 어디 | DataDirectory를 | 한정된?  (0) 2020.04.04
[SQL] NVARCHAR (최대) 아직 잘리지  (0) 2020.04.04
[SQL] 서브 쿼리가 존재합니다 도입되지 않은 경우 하나 개의 표현이 선택 목록에 지정할 수 있습니다  (0) 2020.04.04
,

티스토리툴바