Loading ...
Sorry, an error occurred while loading the content.

How to identify cross-database dependencies?

Expand Messages
  • Senthil Kumar T R
    Hello All, I have this situation:   I have two databases say  db1 and db2 . I have a table tbl1 in the database db1. I have a SP  proc1 in databse db2
    Message 1 of 2 , Jun 21, 2011
    • 0 Attachment
      Hello All,
      I have this situation:
       
      I have two databases say "db1" and "db2".
      I have a table "tbl1" in the database db1.
      I have a SP  proc1 in databse db2 that accesses the data from db1.dbo.tbl1
       
      These are just samples and I have many tables and procdures accessing the objects across the databases.
       
       
      Given this situation, From db1 - Is there an easy way to identify stored procedures of db2 that are depending on the tables of this database?
       
      Similarly, From db2 - How can I identify the tables of db1 that the procedures of db2 are depending on?
       
      It looks like I cannot rely on sys.syscomments as it may not be fool-proof/straight forward.
       
       I tried sp_depends, but that gives the dependencies only within the same database.
       
      Any ideas?
       
      Thanks
      Senthil T R

      [Non-text portions of this message have been removed]
    • ramakrishna_e2
      Hi Senthil, Only way you could find dependencies is using SYSCOMMENTS table & sp_depends stored Proc. Please find below article that will give you more info
      Message 2 of 2 , Jun 21, 2011
      • 0 Attachment
        Hi Senthil,

        Only way you could find dependencies is using SYSCOMMENTS table & sp_depends stored Proc.

        Please find below article that will give you more info and hope this helps you...


        http://www.databasejournal.com/features/mssql/article.php/3571026/Finding-Database-Object-Dependencies.htm

        Thanks,
        Rama

        --- In sqlcon@yahoogroups.com, Senthil Kumar T R <trsenthilkumar@...> wrote:
        >
        > Hello All,
        > I have this situation:
        >  
        > I have two databases say "db1" and "db2".
        > I have a table "tbl1" in the database db1.
        > I have a SP  proc1 in databse db2 that accesses the data from db1.dbo.tbl1
        >  
        > These are just samples and I have many tables and procdures accessing the objects across the databases.
        >  
        >  
        > Given this situation, From db1 - Is there an easy way to identify stored procedures of db2 that are depending on the tables of this database?
        >  
        > Similarly, From db2 - How can I identify the tables of db1 that the procedures of db2 are depending on?
        >  
        > It looks like I cannot rely on sys.syscomments as it may not be fool-proof/straight forward.
        >  
        >  I tried sp_depends, but that gives the dependencies only within the same database.
        >  
        > Any ideas?
        >  
        > Thanks
        > Senthil T R
        >
        > [Non-text portions of this message have been removed]
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.