Find table relationships - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find table relationships

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-05-29 : 16:45:41

Is there any way I can find relationships between tables in SQL 2005? Even a 3rd party tool would work.

Lets say I have a complete data base with 100 tables and a particular table "mytable" is related to 5 other tables. How do I find out the relationships.

Instead of a pictorial view, even a tcsv would work.
So that I get something like this :

table Related table
My table table 1
My table table 3
My table table 5
My table table 7
My table table 9
My table 2 table 1
My table 2 table 3
My table 2 table 15
My table 2 table 17

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 04:02:34

Have you tried this?

select distinct object_name(id),object_name(depid) from sys.sysdepends
order by 1

Madhivanan

Failing to plan is Planning to fail

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-06-01 : 22:06:09

Hello..

I tried
select distinct object_name(id),object_name(depid) from sys.sysdepends
order by 1

But it shows only the dependencies, it doesn't show the relationships

e.g.
select a.col1, a.col2, b.col1, bl.col2
from table1 a
left outer join table 2 on a.id = b.id

In this case I want to see the relationship a.id = b.id.

If I have a readymade database, i have to manually search these relationships and create queries. (Reverse Engineering).

Is there any way teh relatiosnhip can be found out?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 02:28:42

I dont think there is an easy way to do this

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 02:59:46

What is the base of relationship ?

Are you looking for relationship through foreign key constraint or you are just looking column names

for ex if any table have column name deptid
then you are looking for same column name in a different table or what?

because it might be possible that two tables can have same column name
but there is not any relationship by that common column.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-03 : 04:10:19

See function fnTableTree here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-06-03 : 05:19:07

Madhivanan : Looks like its difficult :

Vaibhav : I'm just looking for column names which column of table1 relate to which column of column2. Not based on Foreihn Key but the actual relationship that we use while creating queries.

Peso : Apologies for my ignorance. But the thread has become very long in the link and its is confusig. What is the code that I should be using so that it shows all relations of a table with other tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-03 : 14:53:24

It seems all you want is this!?

DECLARE	@TableName SYSNAME = 'dbo.MyTableNameHere'

SELECT	'Parent tables' AS TableConnectionType,
	OBJECT_SCHEMA_NAME(referenced_object_id) AS obj_schema_name,
	OBJECT_NAME(referenced_object_id) AS obj_name
FROM	sys.foreign_keys
WHERE	parent_object_id = OBJECT_ID(@TableName)

UNION

SELECT	'Child tables' AS TableConnectionType,
	OBJECT_SCHEMA_NAME(parent_object_id) AS obj_schema_name,
	OBJECT_NAME(parent_object_id) AS obj_name
FROM	sys.foreign_keys
WHERE	referenced_object_id = OBJECT_ID(@TableName)

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-06-04 : 00:20:15

I get thr following error :

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@TableName".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@TableName".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 03:15:00

Try this

DECLARE @TableName SYSNAME
SET @TableName = 'dbo.MyTableNameHere'

SELECT 'Parent tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(referenced_object_id) AS obj_schema_name,
OBJECT_NAME(referenced_object_id) AS obj_name
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(@TableName)

UNION

SELECT 'Child tables' AS TableConnectionType,
OBJECT_SCHEMA_NAME(parent_object_id) AS obj_schema_name,
OBJECT_NAME(parent_object_id) AS obj_name
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@TableName)

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 04:36:50

quote:
Originally posted by s_anr

Vaibhav : I'm just looking for column names which column of table1 relate to which column of column2. Not based on Foreihn Key but the actual relationship that we use while creating queries.

Try this -

This might be useful for you..
Solution of Peso and madhi will work
if any defined relationship exists amonng tables

let me know what do you think about this query


SELECT name ColumnName, OBJECT_NAME(c.object_ID) AS TableName 
FROM sys.columns c
WHERE name IN
(
SELECT name FROM sys.columns
GROUP BY name having count(name) > 1
)
ORDER BY Name

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-06-04 : 08:16:08

Thank you guys for your response :

Madhivanan's query doesn't give me any results though the table exists (and has relatioships with other tables : its columns are related to columns of other tables).It runs successfully without results.

Vaibhav's query has given column and table names but how do i find the relationships

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 09:37:20

quote:
Originally posted by s_anr

Thank you guys for your response :

Madhivanan's query doesn't give me any results though the table exists (and has relatioships with other tables : its columns are related to columns of other tables).It runs successfully without results.

Vaibhav's query has given column and table names but how do i find the relationships

I am still not clear which kind of relationship you are talking.
let me explain -
Suppose i have one table
mstemployee with the columns names empid, empname, address, city, deptid, designationid
another tables
mstdepartment with the columns deptid, DepartmentName
and
mstDesignation with the columns designationid, DesignationName

Now when you run my query it will give you output


ColumnName	TableName
deptid		mstEmployee
deptid		mstDepartment
DesignationID	mstEmployee
DesignationID	mstDesignation

Now it clearly shows that deptid is the column which is common in table mstEmployee and mstDepartment
so while writing queries you can join these two tables with that common column.

What else you are expecting please let us know to help you more....

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-06-04 : 10:02:08

Thanks Vaibhav. I'll try yo explain again :

Lets say we have 3 tables
mstemployee with the columns names
empid
empname
address
city
dept
designation

mstdepartment with the columns
deptid
DepartmentName

mstDesignation with the columns
designationid
DesignationName

The relationship is

mstemployee.dept=mstdepartment.deptid
mstemployee.designation=mstdepartment.designation

I need to find this relationship

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources