Small Doubt on Tsql

Question

Small Doubt on Tsql

IN_Sandeep

SSCertifiable

Points: 6259
More actions
October 10, 2008 at 3:16 am

#194431

All,
I have a very small doubt on my query below.
actually I have a inner procedure which is called by 2 more stored procedure.
when i am applying the command sp_depends innerpoc name its showing only one proc name instead of 2 proc name.
i applied the sp_updatestats but its not showing the all the proc name..
can u guide me please..
Cheers!
Sandy.
--

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

which version of SQL Server are you working with? I have heard that some times the dependency information is not updated correctly. But i could not experience such a case so far.

If you are on SQL server 2005, run the script given here: http://www.sqlserverandxml.com/2008/09/find-dependent-objects-recursively.html and see if it shows the dependencies correctly.

.

jacob sebastian SSChampion Points: 11822 More actions · Answer 2

Try this script and see if you get the same results:

CREATE PROCEDURE innerproc as

select 0

go

create procedure outerproc1 as

execute innerproc

go

create procedure outerproc2 as

execute innerproc

go

sp_depends innerproc

/*

name

-----------------------------

dbo.outerproc1

dbo.outerproc2

*/

.

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

Hi,

lets assume the database is Old and sql 2000, so how can i update it?

Cheers!

Sandy.

--

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

Honestly i've always avoided using SP_DEPENDS because of it's unreliability. I suppose you could try to do ALTER PROCEDURE commands of the inner stored procs then ALTER PROCEDURE commands of the outer stored procs if the problem was the order of when the procedures were created or applied to the database.

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 5

sysdepends doesn't "fix" things created out of order. What are you trying to do, update it? There might be some things that can do this, but why do you need this?

Red Gate (I work for them) has a Dependency Tracker they sell precisely for this reason.

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

Hi Steve,

I need this because in my application its necessary to know the dependency for the Stored Procedure objects. If this is the situation how can handle it. and for your information we are using a DB which is build on 2002 and its in 2000 version.

Can you please help me for the same?

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Try dropping all three procs, recreate the inner one and then recreate the two outer ones.

If you create the dependant object before the object that it depends on (allowed in T-SQL) then sysdepends will not reflect the dependencies. Recreating the objects is the only fix that I know of.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

Gail,

I agree with you, but for that you need the know the inner and out proc too. rite?. and the DB contains more than 750 Procedure and Its not that much of simple to find out the inner and outer one. again the inner proc contains some inner proc level too. thats why, needed one simple command which will display the like below..

SP name dependency name Level

======= =============== ====

So, easily find out the procedure name and inner level too.

do you have any command then just tell me.

Cheers!

Sandy.

--

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 9

Try the following way, it may help to you.

selectdistinct object_name (id) from syscomments where text like '% %'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

So, easily find out the procedure name and inner level too.
do you have any command then just tell me.

No easy way I know of on SQL 2000 or 2008. 2008 has a dependency system that's apparently trustworthy.

If the procs aren't going to change often, maybe see if you can get Redgate's SQL Dependency tracker and see if it can output to a table, then query the table. Problem with that is it won't update as procs change.

You can do the same thing manually by querying the system tables syscomments/sys.sql_modules and create your own dependency list. Won't be easy though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 11

check out this post..

http://www.sqlservercentral.com/Forums/Topic571247-338-1.aspx

"Keep Trying"

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 12

Gail,

I think it needs more ground work to make it proper.

Let me think on this (perhaps this weekend) and come up with a quick solution to manage this type of situation.

Thanks Gail,

Cheers!

Sandy.

--