这应该做到这一点：
SELECt OO.Animal_ID, OO.Name, CASE WHEN XX.REFERENCED IS NULL THEN 'false' ELSE 'true' END ReferencedFROM Animal OO OUTER APPLY (SELECt SUM(1) REFERENCED FROM (SELECt FkAnimal_ID FROM AnimalRef1 RR WHERe RR.FkAnimal_ID = OO.Animal_ID UNIOn ALL SELECt FkAnimal_ID FROM AnimalRef2 RR WHERe RR.FkAnimal_ID = OO.Animal_ID UNIOn ALL SELECt FkAnimal_ID FROM AnimalRef3 RR WHERe RR.FkAnimal_ID = OO.Animal_ID) II) XX
如果您不了解所有FK表，则可以使用系统元数据表将UNIOn ALL查询的集合生成到表中，然后可以将其复制并粘贴到查询中：
WITH AKT AS ( SELECt f.name AS ForeignKey ,OBJECT_NAME(f.parent_object_id) AS TableName ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName ,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id WHERe f.referenced_object_id = object_id('Animal'))SELECt 'SELECT ' + ColumnName + ' FROM ' + TableName + ' WHERe RR.' + ColumnName + ' = OO.' + ReferenceColumnName + ' UNIOn ALL'FROM AKT对于使用递归CTE的单个查询中的全部内容：
DECLARE @QUERY NVARCHAr(MAX)WITH AKT AS ( SELECt ROW_NUMBER() OVER (ORDER BY f.name) RN, f.name AS ForeignKey ,OBJECT_NAME(f.parent_object_id) AS TableName ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName ,SCHEMA_NAME(oo.schema_id) SchemaName ,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 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 oo ON oo.object_id = fc.referenced_object_id WHERe f.referenced_object_id = object_id('Animal')) ,bs AS (SELECt AKT.RN ,'SELECT ' + ColumnName + ' FROM ' + SchemaName + '.' + TableName + ' WHERe ' + ColumnName + ' = OO.' + ReferenceColumnName SubQuery FROM AKT) ,re AS (SELECt bs.RN, CAST(RTRIm(bs.SubQuery) AS VARCHAr(MAX)) Joined FROM bs WHERe bs.RN = 1 UNIOn ALL SELECt bs2.RN, CAST(re.Joined + ' UNIOn ALL ' + ISNULL(RTRIm(bs2.SubQuery), '') AS VARCHAr(MAX)) Joined FROM re, bs bs2 WHERe re.RN = bs2.RN - 1 ) ,fi AS (SELECt ROW_NUMBER() OVER (ORDER BY RN DESC) RNK, Joined FROM re)SELECt @QUERY = 'SELECT OO.Animal_ID, OO.Name, CASE WHEN XX.REFERENCED IS NULL THEN ''No'' ELSE ''Yes'' END ReferencedFROM Animal OO OUTER APPLY (SELECt SUM(1) REFERENCED FROM (' + Joined + ') II) XX'FROM fiWHERe RNK = 1EXEC (@QUERY)
