SQL Server: Howto get foreign key reference from information_schema?

user_155077 0
Viewed 60406

In SQL Server, how can I get the referenced table + column name from a foreign key?

Note: Not the table/column where the key is in, but the key it refers to.

Example:

When the key [FA_MDT_ID] in table [T_ALV_Ref_FilterDisplay]. refers to [T_AP_Ref_Customer].[MDT_ID]

such as when creating a constraint like this:

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

I need to get [T_AP_Ref_Customer].[MDT_ID] when given [T_ALV_Ref_FilterAnzeige].[FA_MDT_ID] as input

information-schema foreign-keys sql-server sql
4 Answers

I wanted a a version that would let me find all the "Key" and "ID" columns having/missing a constraint. So i wanted all columns compared to the list of all PK OR FK OR Null, here is my query. Hope it helps someone else!

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 
;

formatting courtesy of: http://www.dpriver.com/pp/sqlformat.htm

user_34953220 
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*
user_111156170
Related