问题描述
我想从这个表中返回所有的内容+表示记录是否被其他地方引用为外键的一列。
> IE：
Animal_Id名称
1 Cat
2狗
3 Parrot
我想返回这个：
AnimalId名称引用
1猫真
2狗假
3 Parrot真
select count（*）from eachTable其中AnimalID = 1
有没有人有关于如何做这件事的片段？
解决方案
SELECT OO.Animal_ID，OO.Name，CASE WHEN XX.REFERENCED IS NULL THEN'false'ELSE'true'END引用
从动物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表，你可以使用system met一个数据表来生成一个UNION ALL查询集合到一个表中，然后你可以复制&粘贴到您的查询中：
pre $ 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
pre>
对于使用递归CTE的单个查询来说，整个事情是这样的：
DECLARE @QUERY NVARCHAR（ MAX）
加上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'+ Co （bs.SubQuery）AS VARCHAR（MAX））加入
FROM（a）（bs.RN，CAST（RTRIM（bs.SubQuery）AS VARCHAR（MAX））lumnName +'= OO。'+ ReferenceColumnName SubQuery
FROM AKT） bs
WHERE bs.RN = 1
UNION ALL
SELECT bs2.RN，CAST（re.Joined +'UNION ALL'+ ISNULL（RTRIM（bs2.SubQuery），''）AS VARCHAR（MAX））加入
FROM re，bs bs2
WHERE re.RN = bs2.RN - 1）
，fi AS（SELECT ROW_NUMBER（）OVER（ORDER BY RN DESC）RNK ，加入
FROM re）
SELECT @QUERY ='SELECT OO.Animal_ID，OO.Name，CASE WHEN XX.REFERENCED IS NULL THEN'NO''ELSE''是''END引用
从动物OO
OUTER APPLY（SELECT SUM（1）REFERENCED
FROM（'+ Joined +'）II）XX'
FROM fi
RNK = 1
EXEC（@QUERY）
I have table Animal.
I want to return everything from this table + one column which denotes if record is referenced anywhere else as a foreign key.
I.E.:
Animal_Id Name
1 Cat
2 Dog
3 Parrot
I want to return this:
AnimalId Name Referenced
1 Cat true
2 Dog false
3 Parrot true
by 'referenced' I mean if a specific Animal_Id was referenced in any other table in the database as a foreign key. I think I first can query information_schema and find out what tables contain this foreign key and then use loop and dynamic sql to execute
select count(*) from eachTable where AnimalID = 1
Does anyone have a snippet on how to do that?
解决方案 This should do it:
SELECT OO.Animal_ID, OO.Name, CASE WHEN XX.REFERENCED IS NULL THEN 'false' ELSE 'true' END Referenced
FROM 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
If you don't know all the FK tables, you can use system meta-data tables to generate that collection of UNION ALL queries into a table, which you could then copy & paste into your query:
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
And for the whole thing in a single query using a recursive 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 Referenced
FROM Animal OO
OUTER APPLY (SELECT SUM(1) REFERENCED
FROM (' + Joined + ') II) XX'
FROM fi
WHERE RNK = 1
EXEC (@QUERY)
这篇关于查询是否在数据库的其他地方引用了外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！