Post reply

Dependent objects

  • June 1, 2006 at 7:55 am

    #113152

    Does anyone have a T-SQL script that will extract a list of all dependent objects in a database?  I would really like a query that shows object X which depends on objects Y and Z.  The biggest problem I'm having is with views that are dependent on tables in a linked server.  Any help would be greatly appreciated.  Thanks

  • June 1, 2006 at 8:25 am

    #641197

    http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm

  • June 1, 2006 at 1:54 pm

    #641299

    Dynamic SQL would give you insurmountable problems in getting dependencies from programmable objects' code. A complete SQL parser would be handy. In the absence of that, a starting point might be something like:

    select

    distinct object_name(c.id)

    from

    syscomments c left join syscomments c2 on c.id = c2.id and c.colid = c2.colid+1

    where

    c.text + coalesce(c2.text,'') like '%sysobjects%'

    This will get the names of all objects whose code contains the specified string. It's a long way from a full answer, though.

    Another approach would involve actually executing the objects (or perhaps even just parsing/compiling them) and getting hold of the execution plans or something along those lines. I'll have a think.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 1, 2006 at 4:38 pm

    #641321

    Thank you both for these answers.  I've been playing around with SQL Dependency Tracker.  It's nice but what I would really like is a report that I can go back and forth looking through when I have issues.  I can't seem to do that with Dependency Tracker; maybe I just haven't found it yet.  I will work with the code.  I'm optimistic that I can modify it to do what I need.

  • June 1, 2006 at 6:03 pm

    #641331

    So if you create a sp which references a table that doesn't exist, then create the table afterwards, the dependency tracker spots it? i.e. it doesn't just use system tables, but actually parses your code (or otherwise finds out what it does?)

    code example of problem with system tables and deferred name resolution:

    create

    proc dbo.testdepend

    as
    select

    * from dbo.doesnt_exist

    go

    create

    table dbo.doesnt_exist(col1 int)

    go

    select

    * from sysdepends where object_name(id) = N'testdepend' or object_name(depid) = N'doesnt_exist'

    go

    drop

    proc dbo.testdepend

    go

    create

    proc dbo.testdepend

    as
    select

    * from dbo.doesnt_exist

    go

    select

    * from sysdepends where object_name(id) = N'testdepend' or object_name(depid) = N'doesnt_exist'

    go

    drop

    proc dbo.testdepend

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

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

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