"Display Dependencies" not showing all dependencies

[David C]

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.

For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.

When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.

There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?

[Aaron [SQL Server MVP]]

Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Post by David C
Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?

[David]

So do you have a suggestion?

I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.

If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?

Post by Aaron [SQL Server MVP]
Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Post by David C
Within Sql Server Enterprise, I click on an object to show dependencies,

and

Post by David C
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT

against

Post by David C
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in

Enterprise

Post by David C
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they

do

Post by David C
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?

[Aaron [SQL Server MVP]]

Post by David
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?

In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).

For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.

And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.

[David]

Post by Aaron [SQL Server MVP]
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).

How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?

Post by Aaron [SQL Server MVP]
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies
functionality,
and can't explain why they bothered putting it into the product.

Your point is well taken.

[Aaron [SQL Server MVP]]

Post by David
How does one recompile all the stored procedures with one command?

If you alter one table, do you have to recompile all stored procedures?
Unlikely.

Post by David
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?

This will generate the command for procs and functions, but not run it.

SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES

For views,

SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'

In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.

Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.