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 ChildrenColumnIdFROM 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_NAMEkmkmmm |
|
|
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.aspxIf 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 Studiohttp://www.youtube.com/watch?v=wMbPRHeYvMU |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-03 : 16:16:52
|
Thanks to All :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-03 : 23:37:26
|
welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 databaseUSE yourDatabasenameGOEXEC sp_depends 'tablename' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-09 : 06:38:02
|
Thanks Visakh it works now |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-09 : 11:51:57
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|