如果我有一张桌子,TableA：
Id 1 2 3 ...
还有另外两张桌子：
表B：
Id,TableAId 1 1 2 1
表C：
Id,TableAId 1,1 2,2
TableAId与TableA.Id的FK关系.
如何确定TableA,Id 1有三行指向它？那个TableA,Id 2有一行指向它吗？更具体地说,我如何识别这些行是什么？ (他们的表名和Id)
解决方法
您可以使用INFORMATION_SCHEMA视图生成select语句以显示相关行.我只针对问题中提供的表进行了测试,但是在密钥为多列的情况下可以扩展它.
declare @table_schema nvarchar(50) = 'dbo',@table_name nvarchar(50) = 'TableA',@id int = 1
select fk_col.TABLE_SCHEMA,fk_col.TABLE_NAME,fk_col.COLUMN_NAME,'select * from ' + fk_col.TABLE_SCHEMA + '.' + fk_col.TABLE_NAME + ' t1 '
+ ' inner join ' + @table_schema + '.' + @table_name + ' t2 '
+ ' on t1.' + fk_col.COLUMN_NAME + ' = t2.' + pk_col.COLUMN_NAME
+ ' where t2.' + pk_col.COLUMN_NAME + ' = ' + cast(@id as nvarchar)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_col
on pk.CONSTRAINT_SCHEMA = pk_col.CONSTRAINT_SCHEMA
and pk.CONSTRAINT_NAME = pk_col.CONSTRAINT_NAME
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
on pk.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA
and pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_col
on fk_col.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
and fk_col.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
where pk.TABLE_SCHEMA = @table_schema
and pk.TABLE_NAME = @table_name
and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
生成的select语句：
select * from dbo.TableB t1 inner join dbo.TableA t2 on t1.TableAId = t2.Id where t2.Id = 1 select * from dbo.TableC t1 inner join dbo.TableA t2 on t1.TableAId = t2.Id where t2.Id = 1
和查询结果：
Id TableAId Id ----------- ----------- ----------- 1 1 1 2 1 1 Id TableAId Id ----------- ----------- ----------- 1 1 1
