Get the mapping tables between two tables.

Question

0.00/5 (No votes)

See more:

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.

Posted 3-Apr-17 20:52pm

smodak@ats360.com

Updated 4-Apr-17 1:48am

Add a Solution

Comments

Tomas Takac 4-Apr-17 3:35am

Yes, it is definitely possible, using schema tables. But it seems suspicious. Are you sure there is no other way? BTW put your current code in the "What I have tried" section.

smodak@ats360.com 5-Apr-17 3:06am

I have tried many schema combination queries to get the connection between two tables but could not come up with proper solution yet.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-04-04T01:48:00

Solution 1

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

Posted 4-Apr-17 1:48am

CHill60

Comments

smodak@ats360.com 5-Apr-17 3:05am

Thanks for reply

I have already tried above query but it is not giving the desire result.
I need the connection between two tables to form a query.

CHill60 5-Apr-17 3:58am

Give an example: The query above gives you nearly enough information to form a SQL query - all that is missing is the Primary key for the base table (in the example above, 'Employees')

smodak@ats360.com 6-Apr-17 3:07am

Please consider provided example of table structure of A,B and C.
If I write the query as follows
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 = 'A'
order by 1, 2

It gives me only table B foreign key reference info. I could not get the C table info. Which are related to table B. There can be n number of hierarchy. I want something like I could provide tables A and C to query and it will give information of B table.

CHill60 6-Apr-17 3:09am

Try using where t2.name IN ('A','B') and see what you get then