Just Code‎ > ‎

SQL - Simple script to get referenced table and their column names

posted Jun 10, 2010, 11:33 AM by Peter Henell

http://weblogs.sqlteam.com/peterl/archive/2010/06/08/Simple-script-to-get-referenced-table-and-their-column-names.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+SqlteamcomWeblogs+(SQLTeam.com+Weblogs)



by Peter Larsson

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