sql server get relational table sql

 

Si riporta l'istruzione SQL da eseguire nel database Sql Server per ottenere informazioni sulle relazioni di un database.

SELECT

    forKeys.name 'Nome Relazione',

    tp.name 'Tabella Principale',

    cp.name as 'Nome Colonna', 

    tr.name 'Tabella di riferimento',

    cr.name as ' Colonna di riferimento'

FROM

    sys.foreign_keys forKeys

INNER JOIN

    sys.tables tp ON forKeys.parent_object_id = tp.object_id

INNER JOIN

    sys.tables tr ON forKeys.referenced_object_id = tr.object_id

INNER JOIN

    sys.foreign_key_columns fkc ON fkc.constraint_object_id = forKeys.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

ORDER BY

    tp.name, cp.column_id