sp_depends - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

sp_depends

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 09:37:22

Why Would sp_depends, NOT show the sprocs that references a table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-26 : 09:41:39

dynamic sql, different database, table has been recreated with a different id (though it caught that now though), referenced via a view (don't know if it picks that up).

Have a look at sp_depends and see why it's missing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 09:50:09

quote:
Originally posted by nigelrivett

dynamic sql (None, heaven forbid), different database (Nope), table has been recreated with a different id (though it caught that now though)(Nope, dbo.), referenced via a view (don't know if it picks that up)(Nope).

Have a look at sp_depends and see why it's missing.(Not yet)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 09:55:24

[code]
select parsename('User_Message',3)
select object_id('User_Message')

select *
from sysdepends
where id = 900198257
[/code]

I get no rows back from sysdepends for the object_id it tells me

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-26 : 11:05:01

quote:
Originally posted by X002548

select parsename('User_Message',3)
select object_id('User_Message')

select *
from sysdepends
where id = 900198257

I get no rows back from sysdepends for the object_id it tells me

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

What is that ???

Personally I never relied on sp_depends.Instead I use the SSMS as it gives a nice tree view when you right click the View Dependencies option on any object.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 12:43:27

What "THAT" is, is part of the Guts of sp_depends

sp_helptext sp_depends

Have a look

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 12:57:32

quote:
Originally posted by X002548

[quote]Originally posted by nigelrivett

table has been recreated with a different id (though it caught that now though)(Nope, dbo.)

Different ID, not different schema. So table dropped & recreated, or created after the procedure.

sp_depends (in fact the entire pre-2008 dependencies) had more holes than swiss cheese

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 13:12:07

This is 2K8R2

Let's say the table was dropped...recompile the sprocs?

I thought they were tagged for recompile after an object was dropped...automagically..let recompile them manually

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 13:19:56

...and that was the Answer

Thanks Gail

I thought that would have forced a recompile on a drop and create

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 15:03:32

quote:
Originally posted by X002548

...and that was the Answer

Thanks Gail

I thought that would have forced a recompile on a drop and create

Sure a drop/create forces recompiles of plans. Plans and recompiles however have zip, zero, nothing to do with dependencies though.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 15:15:27

...of plans....not the sproc itself???

I was misinformed

or more likely, misunderstood..

How can you get a new plan if the sproc isn't recompiled

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-26 : 15:34:18

quote:
Originally posted by X002548

...of plans....not the sproc itself???

Semantics. A procedure has a plan. A recompile recreates part or all of a plan.

quote:
How can you get a new plan if the sproc isn't recompiled

Plan gets aged out of cache. Plan becomes invalid for one of several reasons (though that's called a recompile). SQL restarts. Something clears the plan cache (restore/detach/drop/offline a DB, reconfigure). There's a few more things too.

None of these have anything to do with dependencies though.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 15:54:48

When I manually recompiled the sprocs..sp_depends then works

oil well

Good stuff to know

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-27 : 13:38:53

"When I manually recompiled the sprocs"

You mean DROP / CREATE (or re-create using ALTER perhaps ...) ?

or


EXEC sp_recompile 'MySproc'

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 14:32:26

I did (and always do) Drop and Create

sp_recompile I guess would have worked as well

ALL of my Sprocs contain the check for existence and does the drop

The script also contains all of the grants and SET's that I want for each sproc

Never liked ALTER..what if it's stored a bad plan..as I've seen happen..

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-27 : 15:16:02

quote:
Originally posted by X002548

I did (and always do) Drop and Create

sp_recompile I guess would have worked as well

No it wouldn't. All sp_recompile does is mark the procedure's plan for recompile, nothing more.

Drop/create fixed sp_depends because the create set up the dependencies.

quote:
Never liked ALTER..what if it's stored a bad plan..as I've seen happen..

Altering a procedure removes its plan from cache. Has to, because the new procedure could have some completely different code in it.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 15:18:11

Well, then..in my ignorance.....I am safe

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 05:32:04

We've moved to using ALTER (with a CREATE of a dummy shell sproc first IF NOT EXISTS).

Reason is that when we have to issue a fix on a production system it either runs, or leaves the original in place, and there seems to be less (not entirely sure there is NO) disruption to users.

Our CREATE has:

DROP PROCEDURE ...
CREATE PROCEDURE ...
GRANT EXECUTE ...

so I suppose there was a window of opportunity between the two for users to find the sproc, but not to have permissions. And presumably plenty of window of opportunity to find no sproc! between the DROP and the CREATE.

But for a scheduled rollout during downtime its probably a moot point. (Although DROP/CREATE will leave the sproc missing if there is an error during create, whereas ALTER will leave the original in place which might not be spotted). Perhaps I should add a test for Update Date after all rollout scripts have been done ...

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 09:51:43

drop
create
if failure
create old sproc

???

Can you trap a sproc compile failure???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 11:02:29

"Can you trap a sproc compile failure???"

Using a CREATE then it won't EXIST if there is a syntax error / failure.

Not sure how I could be confident to have the "right version"" of Old Source. Maybe:


IF EXISTS MyTempSproc drop MyTempSproc
RENAME MySproc to MyTempSproc
create MySproc
IF NOT EXISTS MySproc THEN RENAME MyTempSproc to MySproc

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 11:03:23

I suppose I could do something similar to insert a row into an ErrorLoggingTable and display that at the end of the script to alert me to things that had failed. Might make me complacent checking the output from the Rollout Script ... or might save me from myself on a bad day!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources