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
 how to which tables are linked to each other

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-01 : 18:20:38
I'm using an application which uses sql server database. When I open the database in sql server, it has many tables, however I don't know which tables are linked to which ones. Suppose if I enter any values or delete any values in a table, I would like know where else it would affect in the database.

Is it possible to know that?

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-01 : 19:06:06

use mydatabase;
SELECT
t2.TABLE_NAME,
t2.COLUMN_NAME,
t3.TABLE_NAME AS ChildrenTableName,
t3.COLUMN_NAME AS ChildrenColumnId
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS t1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t2 ON t1.UNIQUE_CONSTRAINT_NAME = t2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t3 ON t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
--WHERE
ORDER BY t2.TABLE_NAME

kmkmmm
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-01 : 19:32:50

In additon, here are few more things you can do:

You can use the system stored procedure sp_depends ,
it returns the database object dependencies.
You can find out more about this procedure at:
http://msdn.microsoft.com/en-us/library/ms189487.aspx

If you know a specific table that you want more information about, you can run the following command:

Usage:
[CODE]
EXEC SP_DEPENDS [TABLENAME]
[/CODE]

Once you get the list of views and stored procedure that depend on the table, analyzing view and stored procedure definitions will give you further details.

The second thing you can do is create database diagram (if one already doesnt exists) that shows you the relationships between your tables;
You can find more on this topic here:
How to Create a Database Diagram Using SQL Server Management Studio
http://www.youtube.com/watch?v=wMbPRHeYvMU
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 20:23:57
see
http://visakhm.blogspot.in/2012/03/advantages-of-using-syssqlmodules-view.html
http://visakhm.blogspot.in/2010/01/finding-cross-server-cross-db-object.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 16:16:52
Thanks to All :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 23:37:26
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-08 : 08:34:13
@Mumu88, I get error "the table not found in master database" for your code.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 13:52:25
quote:
Originally posted by learning_grsql

@Mumu88, I get error "the table not found in master database" for your code.




You should run it in proper database

USE yourDatabasename
GO
EXEC sp_depends 'tablename'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-06-09 : 06:38:02
Thanks Visakh it works now
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-09 : 11:51:57
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -