Here's a routine I developed a few years ago to query ad hoc parent/child relationships for a given table:
---------------------------------------------------------------------
-- Name: FKRelationships
-- Purpose: Map all parent/child relationships to the selected table
-- Date: 1/19/2009
-- Author: John W. Dewey
-- Instructions: 1) Connect this script to the database containing
-- the table you want to analyze
-- 2) Set the schema name and table name variables
-- Initializations section below. Be sure the table
-- name variable does not contain the schema name
-- 3) You will get two result sets; the first will show
-- the relational parent tables to your selected table, and
-- the second will show the relational children to your
-- selected table.
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Declarations
declare @TableName nvarchar(128)
declare @SchemaName nvarchar(128)
declare @ParentTableSchema nvarchar(128)
declare @ParentTableName nvarchar(128)
declare @FKName nvarchar(128)
declare @Column nvarchar(128)
declare @Table nvarchar(128)
declare @Schema nvarchar(128)
declare @FKColumnString nvarchar(max)
declare @PKColumnString nvarchar(max)
declare @result table (
[Parent Table] nvarchar(128)
, [Child Table] nvarchar(128)
, [Constraint] nvarchar(128)
, [Relationship] nvarchar(max)
)
declare @FKTemp table (
[id] int identity(1,1)
, [Child Schema Name] nvarchar(128)
, [Child Table Name] nvarchar(128)
, [Child FK Name] nvarchar(128)
, [Column Name] nvarchar(128)
)
---------------------------------------------------------------------
-- Initializations
set @SchemaName = 'dbo'
set @TableName = 'MyTable'
---------------------------------------------------------------------
-- Tables referenced by the specified table's FKs
declare c1 cursor for
select a.CONSTRAINT_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
where b.TABLE_NAME = @TableName
and b.CONSTRAINT_SCHEMA = @SchemaName
open c1
fetch next from c1 into @FKName
while @@fetch_status=0 begin
-- Build a string of each FK's corresponding columns
set @FKColumnString = @SchemaName+'.'+@TableName+'.'
declare c2 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.COLUMNS b
on a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
where a.CONSTRAINT_NAME=@FKName
and a.TABLE_SCHEMA = @SchemaName
order by b.ORDINAL_POSITION
open c2
fetch next from c2 into @Column
while @@fetch_status=0 begin
set @FKColumnString = @FKColumnString + '+' + @Column
fetch next from c2 into @Column
end
set @FKColumnString = replace(@FKColumnString, '.+', '.')
close c2
deallocate c2
-- Build a string of each PK's corresponding columns
declare c3 cursor for
select distinct c.TABLE_SCHEMA
, c.table_name as [Parent Table Name]
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c
on a.UNIQUE_CONSTRAINT_NAME = c.CONSTRAINT_NAME
where b.table_name = @TableName
and b.TABLE_SCHEMA = @SchemaName
and a.CONSTRAINT_NAME = @FKName
open c3
fetch next from c3 into @ParentTableSchema, @ParentTableName
while @@fetch_status=0 begin
set @PKColumnString = @ParentTableSchema+'.'+@ParentTableName+'.'
declare c4 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE = 'PRIMARY KEY'
where a.TABLE_NAME = @ParentTableName
and a.TABLE_SCHEMA = @ParentTableSchema
order by a.ORDINAL_POSITION
open c4
fetch next from c4 into @Column
while @@fetch_status=0 begin
set @PKColumnString = @PKColumnString + '+' + @Column
fetch next from c4 into @Column
end
set @PKColumnString = replace(@PKColumnString, '.+', '.')
close c4
deallocate c4
fetch next from c3 into @ParentTableSchema, @ParentTableName
insert into @result ([Parent Table], [Child Table], [Constraint], [Relationship]) values (@ParentTableSchema+'.'+@ParentTableName, @SchemaName+'.'+@TableName, @SchemaName+'.'+@TableName+'.'+@FKName, @FKColumnString+' = '+@PKColumnString)
end
close c3
deallocate c3
fetch next from c1 into @FKName
end
close c1
deallocate c1
-- Display results
select
[Child Table] as [Selected Table as Child]
, [Parent Table]
, [Constraint] as [Child Constraint]
, [Relationship] as [Relationship to Parent]
from @result
---------------------------------------------------------------------
-- FKs pointing to the specified table
delete from @result
insert into @FKTemp ([Child Schema Name], [Child Table Name], [Child FK Name], [Column Name])
select b.TABLE_SCHEMA as [Child Schema Name]
, b.TABLE_NAME as [Child Table Name]
, a.CONSTRAINT_NAME as [Child FK Name]
, b.Column_Name as [Column Name]
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c
on a.UNIQUE_CONSTRAINT_NAME = c.CONSTRAINT_NAME
and c.TABLE_NAME = @TableName
and c.TABLE_SCHEMA = @SchemaName
inner join INFORMATION_SCHEMA.COLUMNS d
on d.TABLE_SCHEMA = c.TABLE_SCHEMA
and d.TABLE_NAME = c.table_name
and d.COLUMN_NAME = b.COLUMN_NAME
order by d.ORDINAL_POSITION
-- Selected table's PK
set @PKColumnString = @SchemaName+'.'+@TableName+'.'
declare c1 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE = 'PRIMARY KEY'
where a.TABLE_NAME = @TableName
and a.TABLE_SCHEMA = @SchemaName
order by a.ORDINAL_POSITION
open c1
fetch next from c1 into @Column
while @@fetch_status=0 begin
set @PKColumnString = @PKColumnString + '+' + @Column
fetch next from c1 into @Column
end
set @PKColumnString = replace(@PKColumnString, '.+', '.')
close c1
deallocate c1
-- Child tables' FKs
declare c1 cursor for select distinct [Child Schema Name], [Child Table Name] from @FKTemp
open c1
fetch next from c1 into @Schema, @Table
while @@fetch_status=0 begin
set @FKColumnString = @SchemaName+'.'+@Table+'.'
declare c2 cursor for select [Child FK Name], [Column Name]
from @FKTemp
where [Child Table Name] = @Table
open c2
fetch next from c2 into @FKName, @Column
while @@fetch_status=0 begin
set @FKColumnString = @FKColumnString + '+' + @Column
fetch next from c2 into @FKName, @Column
end
close c2
deallocate c2
set @FKColumnString = replace(@FKColumnString, '.+', '.') + ' = ' + @PKColumnString
insert into @result ([Parent Table], [Child Table], [Constraint], [Relationship]) values (@SchemaName+'.'+@TableName, @Schema+'.'+@Table, @Schema+'.'+@Table+'.'+@FKName, @FKColumnString)
fetch next from c1 into @Schema, @Table
end
close c1
deallocate c1
-- Display results
select
[Parent Table] as [Selected Table as Parent]
, [Child Table]
, [Constraint] as [Child Constraint]
, [Relationship] as [Relationship Parent]
from @result
