Peter Larsson Blog

Patron Saint of Lost Yaks

Simple script to get referenced table and their column names

by Peter Larsson in  , , ,

-- Setup user supplied parameters
DECLARE @WantedTable SYSNAME
 
SET     @WantedTable = 'Sales.factSalesDetail'
 
-- Wanted table is "parent table"
SELECT      PARSENAME(@WantedTable, 2) AS ParentSchemaName,
            PARSENAME(@WantedTable, 1) AS ParentTableName,
            cp.Name AS ParentColumnName,
            OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName,
            OBJECT_NAME(parent_object_id) AS ChildTableName,
            cc.Name AS ChildColumnName
FROM        sys.foreign_key_columns AS fkc
INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.parent_column_id
                AND cc.object_id = fkc.parent_object_id
INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.referenced_column_id
                AND cp.object_id = fkc.referenced_object_id
WHERE       referenced_object_id = OBJECT_ID(@WantedTable)
 
-- Wanted table is "child table"
SELECT      OBJECT_SCHEMA_NAME(referenced_object_id) AS ParentSchemaName,
            OBJECT_NAME(referenced_object_id) AS ParentTableName,
            cc.Name AS ParentColumnName,
            PARSENAME(@WantedTable, 2) AS ChildSchemaName,
            PARSENAME(@WantedTable, 1) AS ChildTableName,
            cp.Name AS ChildColumnName
FROM        sys.foreign_key_columns AS fkc
INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.parent_column_id
                AND cp.object_id = fkc.parent_object_id
INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.referenced_column_id
                AND cc.object_id = fkc.referenced_object_id
WHERE       parent_object_id = OBJECT_ID(@WantedTable)