June 1, 2006 at 7:55 am
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
June 1, 2006 at 1:54 pm
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:
distinct object_name(c.id)
syscomments c left join syscomments c2 on c.id = c2.id and c.colid = c2.colid+1
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
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
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:
proc dbo.testdepend
* from dbo.doesnt_exist
table dbo.doesnt_exist(col1 int)
* from sysdepends where object_name(id) = N'testdepend' or object_name(depid) = N'doesnt_exist'
proc dbo.testdepend
proc dbo.testdepend
* from dbo.doesnt_exist
* from sysdepends where object_name(id) = N'testdepend' or object_name(depid) = N'doesnt_exist'
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