How to find all procs in a DB which cross reference other databases

  • Hi All
    I need to find all procs from a database which reference tables from another databases. all my DB has just one schema :dbo
    so e.g. if I have a proc Proc1 in DB1 and it refres to some table in DB2, it should come in my list.
    I thought of using this query:
    select distinct object_name(id) from sys.syscomments where text like '%.dbo.%'

    But I am just thinking if there could be a better way or approach to get this list?
    thanks
    sk

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Try this & see whether it helps:

    SELECT
      ObjName = OBJECT_NAME(referencing_id)
    ,  referenced_database_name
    FROM sys.sql_expression_dependencies
    WHERE referenced_database_name IS NOT NULL;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Here's one way: 

    SELECT p.name,
           sed.referenced_database_name,
           sed.referenced_schema_name,
           sed.referenced_entity_name
    FROM   sys.procedures p
           INNER JOIN
           sys.sql_expression_dependencies sed ON p.object_id = sed.referencing_id
    WHERE sed.referenced_database_name IS NOT NULL
    ;

    This will show you all the procedures in the current database that reference objects in other databases, with some caveats.

    First, this just shows you direct references; it won't show you that procedure A calls procedure B, which then depends on an object in another database.
    If desired, though, the query could be modified to show such chains of dependencies. 

    Second (somewhat similar, actually), if you use any dynamic SQL or even simply EXECUTE some constant string that is a T-SQL command (which isn't even dynamic), references from those commands will not show up here.

    You could try to parse all the text in the stored procedures to get those, but even that won't be guaranteed to capture everything (imagine a case of dynamic SQL where the object reference is built over multiple lines using concatenation into a variable, for example).

    Unless you make extensive use of dynamic SQL, though, this should be a good start.

    Cheers!

    Edit: Heh, my slowness to craft responses strikes again. Phil beat me to the general idea. One of these days I'll remember to always refresh the topic before I post 🙂

  • Jacob Wilkins - Tuesday, July 4, 2017 10:08 AM

    Edit: Heh, my slowness to craft responses strikes again. Phil beat me to the general idea. One of these days I'll remember to always refresh the topic before I post 🙂

    Whereas I bashed my answer out in a couple of minutes, you took the time to produce something of quality ... that's not how I'd define slowness.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Me for 1. I don't care who got there first. I got a useful script I wasn't aware I needed until now.
    NICE 1 and a good question S_KUMAR_S.

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

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