recursive sp_depends
Don
It is well known that the output of sp_depends is unreliable becuase
it depends on the order in which objects were created (e.g. if you
create a procedure that refers to a view which has not yet been
created, it will not appear in the output of sp_depends).
What I am looking for is a reliable sp_depends tool i.e. one whose
results are not dependent on the order objects were created. What
would be even better, is a tool such as this that shows *all* the
tables that a procedure refers to. For example, if procedure A refers
to table T and procedure P, then the output would list not only table
T, but also any tables referred to by P (and any tables referred to by
procedures called by P, and so on....)
Any suggestions?
- Dónal.
Ilya Zvyagin 2147483647
"Don" <donalm...@yahoo.co.uk> wrote in message news:aa1c6f54.0201...@posting.google.com...
> It is well known that the output of sp_depends is unreliable becuase
> it depends on the order in which objects were created (e.g. if you
> create a procedure that refers to a view which has not yet been
> created, it will not appear in the output of sp_depends).
A proc can not be created if it refes to a view that does not exists.
If the view is dropped and recreated after creation of the proc
dependences will be lost BUT for the procedure to work properly
you must recreate the procedure too and dependences will
be restored. That's why in a normally supported database
sp_depends is fully reliable.
> What I am looking for is a reliable sp_depends tool i.e. one whose
> results are not dependent on the order objects were created. What
> would be even better, is a tool such as this that shows *all* the
> tables that a procedure refers to. For example, if procedure A refers
I thins the only way would be total parsing of all server objects
code. To do this you will have at least write your own TSQL
parser.
Don
> A proc can not be created if it refes to a view that does not exists.
> If the view is dropped and recreated after creation of the proc
> dependences will be lost BUT for the procedure to work properly
> you must recreate the procedure too and dependences will
> be restored. That's why in a normally supported database
> sp_depends is fully reliable.
A better example than a view and a proc is two procs which call each
other. Obviously, whichever of them is created first, the other will
not exist at that point - therefore surely it is impossible for
sp_depends to provide accurate dependancy information for these procs.
If I'm right about this, how can you say that "in a normally supported
database sp_depends is fully reliable"?
Ilya Zvyagin 2147483647
"Don" <donalm...@yahoo.co.uk> wrote in message news:aa1c6f54.02013...@posting.google.com...
> A better example than a view and a proc is two procs which call each
> other. Obviously, whichever of them is created first, the other will
> not exist at that point - therefore surely it is impossible for
> sp_depends to provide accurate dependancy information for these procs.
It is accurate, but we just can not have cycling dependences.
> If I'm right about this, how can you say that "in a normally supported
> database sp_depends is fully reliable"?
Yes, you are right, but this case is not very important in
practice. Or I should ask you WHY you want to know
all dependences ? My guess whould be to put objects
definitions in your scripts in wight order.
If you write a programm to analize dependences you will
have to break cycle somehow to have a ranged graph of dependences
and one of two procedures will be "lower" in this graph.
So what is you final goal ?
--
--------------------
Ilya Zvyagin
e-mail: mast...@mail.ru - personal, z...@fct.ru - business.
ICQ UID: 29427861(MasterZIV)
Mark Mertel
stuff. Once a database system has been around for a while, it starts to
accumulate a lot of obsolete objects - views, procs mostly, sometimes
tables. It would be useful if when one was tasked with getting rid of this
detritus there was a tool which was accurate, dependable, and complete.
sp_depends just doesn't cut it.
"Ilya Zvyagin 2147483647" <z...@fct.ru> wrote in message
news:10124988...@gatekeeper.fct.ru...
Ilya Zvyagin 2147483647
"Mark Mertel" <mme...@olympus.net> wrote in message news:u5lfkfj...@corp.supernews.com...
> I can think of one reason why this would be useful - cleaning out the old
> stuff. Once a database system has been around for a while, it starts to
> accumulate a lot of obsolete objects - views, procs mostly, sometimes
> tables. It would be useful if when one was tasked with getting rid of this
> detritus there was a tool which was accurate, dependable, and complete.
> sp_depends just doesn't cut it.
Then there would be no use in knowing an object has no depending objects
as even in this case the object can still be valuable for some other reasons.
I undestens your problems but I see no other way to solve them besides
providing good documentations on every object.