Competition: What’s your favorite Redgate tool? Enter now.

Sql Dependency Tracker and SQL Doc: dependency across databa

johnchanthajohnchantha Posts: 3
edited June 13, 2012 2:00PM in SQL Dependency Tracker
Sql Dependency Tracker and SQL Doc: dependency across databases


Can SQL Dependency Tracker v 2.7 tracks objects across databases?

i.e. I have these scenario below:

serverA with tableA

serverA with viewA from tableA

serverB with ViewB from viewA (viewA from serverA)

ServerC with ViewC from viewB (viewB from ServerB).

(Please do not ask why view within a view?)

If tableA is dropped from serverA, what is the impact to other objects across the databases?

Thanks,
[/b]

Comments

  • James BJames B Posts: 1,124 Silver 4
    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

  • 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
  • James BJames B Posts: 1,124 Silver 4
    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:

    sdtd.png

    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

Sign In or Register to comment.