columns - SQL Server: ¿Cómo obtener la referencia de clave externa de information_schema?

information_schema.columns sql server

Question

En SQL Server, ¿cómo puedo obtener el nombre de la tabla + columna a la que se hace referencia desde una clave externa?

Nota: No es la tabla / columna donde está la clave, sino la clave a la que se refiere.

Ejemplo:

Cuando la tecla [FA_MDT_ID] en la tabla [T_ALV_Ref_FilterDisplay] . se refiere a [T_AP_Ref_Customer].[MDT_ID]

como al crear una restricción como esta:

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

Necesito obtener [T_AP_Ref_Customer].[MDT_ID] cuando se me proporcionó [T_ALV_Ref_FilterAnzeige].[FA_MDT_ID] como entrada

Answer 1

No importa, esta es la respuesta correcta:
http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,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 

Answer 2

Quería una versión que me permitiera encontrar todas las columnas "Clave" e "ID" que tienen / falta una restricción. Así que quería todas las columnas en comparación con la lista de todos PK o FK o nulo, aquí está mi consulta. Espero que ayude a alguien más!

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 
; 

formato cortesía de: http://www.dpriver.com/pp/sqlformat.htm

Answer 3

Si puede vivir utilizando las vistas de catálogo de esquema específico de SQL Server, esta consulta devolverá lo que está buscando:

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

No estoy seguro de cómo, si es que lo hace, puede obtener la misma información de las vistas INFORMATION_SCHEMA ....