Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Relationship information between tables situated at different databases Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 5:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 20, 2014 12:10 AM
Points: 3, Visits: 6
Hw can I get All Relationship information between tables situated at different databases in SQL Server...?
Post #1530242
Posted Monday, January 13, 2014 5:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
well, if there is a view or stored procedure that references a different database or a linked server, you can query for that.

take a look at the view sys.sql_expression_dependencies, and pay attention to these columns:

referenced_server_name
referenced_database_name
referenced_schema_name
referenced_entity_name

you may need to do that in each of your databases, since you are trying to discover dependancies,a dn they are "saved" local to each database.

select * 
from sys.sql_expression_dependencies




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1530248
Posted Monday, January 13, 2014 5:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 20, 2014 12:10 AM
Points: 3, Visits: 6
Thanks a Lot for your Reply ... can you plz tell me how can get relationship information between tables on which column is used as a foreign key to refer a first table situated at different database?
Post #1530254
Posted Monday, January 13, 2014 6:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
it's not possible for a foreign key to reference a different database.

foreign keys can only be created within the same database as the two tables exist in.


it's possible to make a function, that returns, say 0/1 if a value exists in another database, and then use THAT to enforce a check constraint.
it's a rare situation where someone puts something like that in place, but my first example would help you find those situations.

so any references can only be inferred by using, say a udf plus a check constraint, or implied by joins in cross database views, maybe.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1530261
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse