获取两个表之间的映射表。 [英] Get the mapping tables between two tables.

问题描述

我在sql中有3个表，如下所示（仅举例）。在这个表中没有直接连接到表C.在表A和C之间有映射表B.

在C＃.net中动态构建查询时我将只获得有关表的信息A和C.在sql中是否有任何方法可以找到A和C之间的连接器表。

由于我动态创建查询，因此两个表之间可以有n个映射表。

有没有办法在两张桌子之间找到连接表？



表主键外键

A A_ID

B B_ID A_ID

C C_ID B_ID





谢谢

Sampada



我尝试过：



我试图使用架构获取此信息。但无法得到确切的结果。

Hi,

I am having 3 tables in sql as below(Just for example). In this Table A is not directly connected to the table C. There is mapping table B in between table A and C.
While dynamically building the query in C#.net I will be having only information about table A and C. Is there any way in the sql where I can find connector tables between A and C.
Since I am creating query dynamically there can be n numbers of mapping tables between two tables.
Is there any way to find connecting tables between two tables?

Table Primary KEY Foreign KEY
A A_ID
B B_ID A_ID
C C_ID B_ID


Thanks
Sampada

What I have tried:

I have tried to get this info using schema. but Not able to get the exact result.

推荐答案

您应该使用 sys 视图来获取此信息< br $>


SELECT t2.name as TableReferenced，

t.name AS TableWithForeignKey，

c.name AS ForeignKeyColumn，

object_name（constraint_object_id）为FK_Name

来自sys.foreign_key_columns为fk

内连接sys.tables为t on fk.parent_object_id = t。 object_id

内部连接sys.columns为c on fk.parent_object_id = c.object_id和fk.parent_column_id = c.column_id

内部连接sys.tables t2 on fk.referenced_object_id = t2.object_id

其中t2.name ='员工'

订购1,2

[注意< pre>在网站错误修复之前故意省略标签]



更好的方法是使用数据库图表工具来记录你的数据库

You should use the sys views to get this information

SELECT t2.name as TableReferenced,
t.name AS TableWithForeignKey,
c.name AS ForeignKeyColumn,
object_name(constraint_object_id) as FK_Name
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
inner join sys.tables t2 on fk.referenced_object_id=t2.object_id
where t2.name = 'Employees'
order by 1, 2
[Note <pre> tags deliberately omitted until the site bug is fixed]

Better yet would be to use the Database Diagramming tool to document your database

这篇关于获取两个表之间的映射表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！