Post reply

Finding dependencies across databases

  • January 17, 2013 at 6:52 am

    #393958

    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

  • January 17, 2013 at 8:21 am

    #1578341

    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

    --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!

  • January 21, 2013 at 4:03 am

    #1579174

    Many thanks, greatly appreciated.

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

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