SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Find table relationships
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

s_anr
Yak Posting Veteran

81 Posts

Posted - 05/29/2010 :  16:45:41  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 05/31/2010 :  04:02:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

s_anr
Yak Posting Veteran

81 Posts

Posted - 06/01/2010 :  22:06:09  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 06/03/2010 :  02:28:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I dont think there is an easy way to do this

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 06/03/2010 :  02:59:46  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30105 Posts

Posted - 06/03/2010 :  04:10:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See function fnTableTree here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

s_anr
Yak Posting Veteran

81 Posts

Posted - 06/03/2010 :  05:19:07  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30105 Posts

Posted - 06/03/2010 :  14:53:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

s_anr
Yak Posting Veteran

81 Posts

Posted - 06/04/2010 :  00:20:15  Show Profile  Reply with Quote
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".
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 06/04/2010 :  03:15:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 06/04/2010 :  04:36:50  Show Profile  Reply with Quote
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

Edited by - vaibhavktiwari83 on 06/04/2010 04:38:36
Go to Top of Page

s_anr
Yak Posting Veteran

81 Posts

Posted - 06/04/2010 :  08:16:08  Show Profile  Reply with Quote
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
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 06/04/2010 :  09:37:20  Show Profile  Reply with Quote
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
Go to Top of Page

s_anr
Yak Posting Veteran

81 Posts

Posted - 06/04/2010 :  10:02:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000