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

Finding dependencies across databases Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:33 AM
Points: 9, Visits: 57
Good afternoon... I wonder if anyone can help me with something which, as is often the way, I thought might be simpler.

I need to provide a list of columns and their associated datatypes that are in tables referenced by a large number of stored procs. Sp_Depends seems to get me most of the way to where I need to be, but alas does not return dependencies where the table being joined to is in a different database.

Does anyone have a brilliant idea that I can use and then look like a hero? Many thanks.

J
Post #1408402
Posted Thursday, January 17, 2013 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:10 PM
Points: 12,909, Visits: 32,012
this can get you started: it shows every dependency that is using a cross database reference:
select 
OBJECT_NAME(referencing_id) As ViewName,
OBJECT_NAME(referenced_id) As ReferencedObject,*
from sys.sql_expression_dependencies
where referenced_database_name is not null

getting the column names from the remote server is going to require some dynamic sql to extract i think, since you cannot use a value in a table to use as part of joined object.


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 #1408454
Posted Monday, January 21, 2013 4:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:33 AM
Points: 9, Visits: 57
Many thanks, greatly appreciated.
Post #1409465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse