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

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)