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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:24 AM
Points: 11, Visits: 61
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: Today @ 6:44 AM
Points: 12,901, Visits: 32,135
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:24 AM
Points: 11, Visits: 61
Many thanks, greatly appreciated.
Post #1409465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse