SQL how do you query for tables that refer to a specific foreign key value?

Question

I have table A with a primary key on column ID and tables B,C,D... that have 1 or more columns with foreign key relationships to A.ID.

How do I write a query that shows me all tables that contain a specific value (eg 17) of the primary key?

I would like to have generic sql code that can take a table name and primary key value and display all tables that reference that specific value via a foreign key.

The result should be a list of table names.

I am using MS SQL 2012.

Answer 1

You want to look at sys.foreignkeys. I would start from http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

to give something like

declare @value nvarchar(20) = '1'

SELECT 
    'select * from '

    + QUOTENAME( SCHEMA_NAME(f.SCHEMA_ID))
    + '.'
    + quotename( OBJECT_NAME(f.parent_object_id) )
    + ' where '
    + COL_NAME(fc.parent_object_id,fc.parent_column_id) 
    + ' = '
    + @value

FROM sys.foreign_keys AS f
 INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
 INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Answer 2

Not an ideal one, but should return what is needed (list of tables):

declare @tableName sysname, @value sql_variant

set @tableName = 'A'
set @value = 17

declare @sql nvarchar(max)

create table #Value (Value sql_variant)
insert into #Value values (@value)

create table #Tables (Name sysname, [Column] sysname)
create index IX_Tables_Name on #Tables (Name)

set @sql = 'declare @value sql_variant
select @value = Value from #Value

'
set @sql = @sql + replace((
select
    'insert into #Tables (Name, [Column])
select ''' + quotename(S.name) + '.' + quotename(T.name) + ''', ''' + quotename(FC.name) + '''
where exists (select 1 from ' + quotename(S.name) + '.' + quotename(T.name) + ' where ' + quotename(FC.name) + ' = @value)
'
from
    sys.columns C
    join sys.foreign_key_columns FKC on FKC.referenced_column_id = C.column_id and FKC.referenced_object_id = C.object_id
    join sys.columns FC on FC.object_id = FKC.parent_object_id and FC.column_id = FKC.parent_column_id
    join sys.tables T on T.object_id = FKC.parent_object_id
    join sys.schemas S on S.schema_id = T.schema_id
where
    C.object_id = object_id(@tableName)
    and C.name = 'ID'
order by S.name, T.name
for xml path('')), '
', CHAR(13))

--print @sql
exec(@sql)

select distinct Name
from #Tables
order by Name

drop table #Value
drop table #Tables