问SQL如何查询引用特定外键值的表？

EN

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

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

CREATE TABLE tempTable
(
     TABLE_NAME varchar(255)
);

CREATE UNIQUE CLUSTERED INDEX Idx_tempTable ON tempTable(TABLE_NAME);

DECLARE @var2 nvarchar(max)

INSERT INTO tempTable 
       SELECT DISTINCT 
              TABLE_NAME 
       FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE COLUMN_NAME LIKE '%COLUMN_NAME%'

/*FOREACH result of the tempTable you could find if the COLUMN_NAME of the result(table) has the value you want*/

SET @var2 = 'SELECT TABLE_NAME FROM ' + tempTableResult + ' WHERE COLUMN_NAME=VALUE'

exec(@var2)

DROP TABLE tempTable