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: 358
Filed under:
-- 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