Simple script to get referenced table and their column names

Posted by Peter Larsson on SQL Team See other posts from SQL Team or by Peter Larsson
Published on Tue, 08 Jun 2010 14:02:34 GMT Indexed on 2010/06/08 14:13 UTC
Read the original article Hit count: 325

-- 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)

 

© SQL Team or respective owner