Sql Dependency Tracker and SQL Doc: dependency across databa

Comments

• James B Redgate › Posts: 1,124 Silver 4

  June 13, 2012 1:09PM

  Thanks for your post - in answer to your query, it's unfortunately a bit of an "it depends".
  
  I set up a simple test -
  
  Table in Instance1\DB1 with a view that selects from it.
  View in Instance2\DB2 that selects from the view in DB1 using the linked server reference.
  
  If I add tables and views from both databases into dependency tracker, the link is correctly resolved- but having said this, we do get reports from people where this doesn't seem to correctly work.
  
  I think it can often come down to complexities of how the objects are linked, the security settings involved, the permissions you have on both instances etc. etc., so the short answer is really to try it and see!

  Systems Software Engineer

  Redgate Software

  0 · Share on Twitter
• johnchantha Posts: 3

  June 13, 2012 1:14PM

  We want to know if SQL Doc and SQL Dependency Tracker can tracks
  objects across databases on the same server for SQL Server 2008 R2.
  
  Below is the sample/demo script.
  
  As part of the reversed engineering, I only know ATable in ADatabase (see below).
  
  If I am to drop Atable from ADatabase, what is the impact to other objects across databases BDatabase and CDatabase (see script).
  
  My initial test appears to indicate that the current version of both SQL Doc and SQL Dependency Tracker have no functionality to perform this task.
  
  Please advise.
  
  Thanks
  John Chantha
  
  
  use master
  go
  
  create database ADatabase
  
  use ADatabase
  go
  
  create table ATable (Id int identity(1,1) not null, IdValue varchar(10))
  
  Insert into ATable(IdValue) values('A Value')
  
  create view vw_A
  as
  select * from ADatabase.dbo.ATable
  go
  
  use master
  go
  
  create database BDatabase
  go
  
  use BDatabase
  go
  
  
  create view vw_B
  as
  select * from ADatabase.dbo.vw_B
  go
  
  use master
  go
  
  
  create database CDatabase
  go
  
  use CDatabase
  go
  
  create view vw_C
  as
  select * from BDatabase.dbo.vw_B
  go

  0 · Share on Twitter
• James B Redgate › Posts: 1,124 Silver 4

  June 13, 2012 2:00PM

  Thanks for that script. I had to modify it slightly (vw_B should select from vw_A) but in any case, both tools seem to pick this up.
  
  SQL Dependency Tracker shows this:
  
  
  
  In addition, SQL Doc shows the reference, for instance going to vw_C shows it "uses" vw_B.
  
  The important thing to note is to add all the DBs to the tool when working with them. If you're only adding one database I'm not sure it'll so easily resolve the other objects. Adding just ADatabase won't show the objects from BDatabase or CDatabase. It works slightly better the other way around- adding just CDatabase will show an unresolved link back to BDatabase. [/img]

  Systems Software Engineer

  Redgate Software

  0 · Share on Twitter