SQL Server to get foreign keys of two tables

Question

In SQL Server, do we have build-it function to get foreign key between two tables?

For example, Table_A, Table_B,

select myfunction ('Table_A', 'Table_B')
-- it should give the column name of its foreign keys, if no any relationship, it return null.

Thanks

Answer 1

SELECT TABLE_NAME
   , CONSTRAINT_NAME
   , COLUMN_NAME
   , ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_NAME IN (
   SELECT CONSTRAINT_NAME
   FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS)
ORDER BY TABLE_NAME, ORDINAL_POSITION;

have a look at here.

Answer 2

You can use something like this:

SELECT 
    FKName = fk.name,
    ParentTable = tpar.name,
    ParentColumn = colpar.name,
    ReferencedTable = tref.name,
    ReferencedColumn = colref.name
FROM
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.tables tpar ON fk.parent_object_id = tpar.object_id
INNER JOIN 
    sys.columns colpar ON fkc.parent_object_id = colpar.object_id AND fkc.parent_column_id = colpar.column_id
INNER JOIN 
    sys.tables tref ON fk.referenced_object_id = tref.object_id
INNER JOIN 
    sys.columns colref ON fkc.referenced_object_id = colref.object_id AND fkc.referenced_column_id = colref.column_id

But of course, you can have multiple foreign keys between TableA and TableB, and they can go "both ways" - you can have on FK going from ParentTable = TableA to ReferencedTable = TableB and another going from ParentTable = TableB to ReferencedTable = TableA .

So to find all foreign keys between two tables, use something like this:

WHERE
    (ParentTable = 'TableA' AND ReferencedTable = 'TableB') OR
    (ParentTable = 'TableB' AND ReferencedTable = 'TableA')

And yes, if you want to - you can wrap this up in a function, if you'd like - I wouldn't recommend doing it, however.

Answer 3

There are a couple of ways to do this. You can use the following:

1. Create a Diagram in your SQL Server Management Studio to view the connections / foreign keys (not a function however)

2. You can run queries using INFORMATION_SCHEMA. This allows you to view all the metadata pulled from the system database. Here is a link to a helpful tutorial: INFORMATION_SCHEMA Tutorial

Additionally, a similar question was addressed at the following StackExchange article. Here is a link: Link to Similar StackOverflow Post