Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Simple script to get referenced table and their column names

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

Print | posted on Tuesday, June 08, 2010 4:02 PM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET