Relationship information between tables situated at different databases

Question

Relationship information between tables situated at different databases

szaware

SSC Rookie

Points: 35
More actions
January 13, 2014 at 5:18 am

#287208

Hw can I get All Relationship information between tables situated at different databases in SQL Server...?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

szaware SSC Rookie Points: 35 More actions · Answer 2

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?

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!