Finding dependencies across databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Finding dependencies across databases

Finding dependencies across databases

Rate Topic

Display Mode

Topic Options

Author

Message

Mr. Cursor

Posted Thursday, January 17, 2013 6:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 11:15 AM
Points: 9, Visits: 37

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

Lowell

Posted Thursday, January 17, 2013 8:21 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 11,614, Visits: 27,673

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

Mr. Cursor

Posted Monday, January 21, 2013 4:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 11:15 AM
Points: 9, Visits: 37

Many thanks, greatly appreciated.

Post #1409465

« Prev Topic | Next Topic »

Permissions