SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab(id INT PRIMARY KEY, name INT);
CREATE TABLE tab2(id INT PRIMARY KEY, name INT);
CREATE TABLE tab_ref(id INT, tab2_ref_id INT REFERENCES tab2(id));
SELECT
    c.TABLE_NAME
    ,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
        (SELECT
                COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        GROUP BY COLUMN_NAME
        HAVING COUNT(*) > 1
        ) dupes
ON dupes.COLUMN_NAME = c.COLUMN_NAME


TABLE_NAME
COLUMN_NAME


tab
id

tab
name

tab2
id

tab2
name

tab_ref
id


SELECT [table] = tab1.name,
       [column] =  col1.name,
       [referenced_table] = tab2.name,
       [referenced_column] = col2.name
FROM sys.foreign_key_columns fkc
JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
JOIN sys.columns col1 ON col1.column_id = parent_column_id 
 AND col1.object_id = tab1.object_id
JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
JOIN sys.columns col2 ON col2.column_id = referenced_column_id 
 AND col2.object_id = tab2.object_id


table
column
referenced_table
referenced_column


tab_ref
tab2_ref_id
tab2
id