June 22, 2005 at 1:56 pm
Does anyone know of a tool that does what sp_depends is supposed to do?
Dumb me, I wrote a tool to document who is calling what, so I can trace what table is accessed by what program. It works great, except it has holes big enough to drive a truck through in it.
That's because I depend on sp_depends to tell me internal SQL dependencies. (Plus a bug I intentionally didn't try to fix that will find more selections in the programs than are real.)
I found there are several procs that don't touch any tables and aren't called by programs. One looked really strange, because it looked like it really should be doing something.
I ran sp_depends on it. It listed several procs that called it, but no procs or tables that it touches. Listing the proc, that made no sense because it's selecting and updating tables left and right and accesses one proc.
The proc made sense because the system depends on the order of creation and our promoting process doesn't always do things in the right order and we drop/create the procs.
The tables didn't make sense, they should have existed before execution and not changed after execution of the proc.
I re-entered the proc using alter. Silly me, I thought the calling procs would still be there when I re-ran sp_depends. It listed the one proc this one calls but no calling procs, no tables.
June 22, 2005 at 10:59 pm
My statements are pretty muddled maybe an example will help
Before I did anything to the proc, the result of 'sp_depends proc_of_interest' is:
In the current database, the specified object is referenced by the following:
name type
--------------------------------- ----------------
dbo.Calling_Proc1 stored procedure
dbo.Calling_Proc2 stored procedure
dbo.Calling_Proc3 stored procedure
dbo.Calling_Proc4 stored procedure
dbo.Calling_Proc5 stored procedure
After I've altered the proc, the result of 'sp_depends proc_of_interest' is:
In the current database, the specified object references the following:
name type updated selected column
-------------------------------- ---------------- ------- -------- ---------
dbo.Called_Proc1 stored procedure no no @iUserID
There are tables it selects from, and tables it updates but they aren't listed. This is the first time I've seen that happen, but I'm getting pretty tired of sp_depends giving me incorrect results in any case. I was hoping that "alter" would at least retain the calling procs in the list.
As a test, I moved this proc to another DB, ran create proc, it gave me the warning message for the proc, but compiled. Hmmm, I thought if you put in tables that don't exist, it would either give the same warning message or kick it out. I'm going to have to look into this a bit more.
June 23, 2005 at 7:01 am
Off the top of my head, I don't really know of a way to get what you're looking for. However, I've seen sp_depends give me the "incorrect" results you're talking about when my procs contain dynamic SQL.
It sounds like that could be the case here too, since you were able to drop the proc into another DB. Niether sp_depends, nor EM/QA will parse the strings in your proc to determine whether or not those strings actually contain more SQL statements.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply