Interrogate stored procedure

Hi all,
Scratching my head and wondering if this is even possible, so thought
I'd ask the collective for some advice before I get started.
I've inherited a stored procedure which contains lots (over 5000 lines)
of calculations and I'd like to be able to work from it, a calculation
and the constituent parts. For example, say the sproc contains -
DECLARE @.a
DECLARE @.b
DECLARE @.c
DECLARE @.d
DECLARE @.e
select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
select @.c = @.d+@.e
select @.a = @.b * @.c
Update mynumbers SET a=@.a, c=@.c where type = 1
Then I'd like to be able to write something, so that I can simply ask
how the sproc calculates a and I would get back -
a = b * c
c = d + e
Does that make sense? Any ideas what would be the easiest way to do
this?
Thanks in advance,
BradBrad,
You could try something like the below.
hth
Ion
/*
* shows assignments using SET in a given procedure.
* assumes that all such assignments are done on single lines
* fails when assignment line breaks across syscomment chunks
* ARGUMENTS
* @.procname the name of the stored procedure
* @.varname the name of the variable the assignment of which is sought
(without the '@.')
*/
create procedure ShowAssignment(@.procname sysname, @.varname
nvarchar(16)) AS
BEGIN
DECLARE @.proctext nvarchar(4000), @.pos smallint
-- collect the text of the procedure
DECLARE proctext CURSOR FAST_FORWARD FOR SELECT syscomments.text
FROM syscomments INNER JOIN sysobjects on syscomments.id =
sysobjects.id WHERE sysobjects.name = @.procname and sysobjects.type =
'p' AND sysobjects.uid = user_id()
OPEN proctext
WHILE @.PROCTEXT IS NULL OR @.@.fetch_status = 0
BEGIn
SET @.pos = 0 -- reinitialize your cursor. Not like a database
cursor. A text cursor.
FETCH FROM proctext INTO @.proctext
-- now, there's no guarantee the proc text won't break in the
middle of an assignment. But, into each life...
WHILE 0 < PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos - 1))
BEGIN
SET @.pos = @.pos + PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos))
IF 0 < charindex(char(13), @.proctext, @.pos)
PRINT SUBSTRING(@.proctext, @.pos, charindex(char(13),
@.proctext, @.pos) - @.pos)
ELSE -- if we break here, show what we've got
PRINT '(partial)' + SUBSTRING(@.proctext, @.pos,
len(@.proctext) - @.pos)
END
END
CLOSE proctext
DEALLOCATE proctext
END
GO|||Hi
Unless type contains unique values you will get potentially random results
from the update.
You could try using
Update mynumbers SET a=b * (d+e), c=(d+e) where type = 1
depending on what you want to achieve.
John
"bradsalmon" wrote:

> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>|||Only a human with the time to read through the code can do what you are
asking.
You can write code that will identify every occurance of the variable in
question, but that will only get you half the answer.
If a = b + c
you still need to know what b and c equal.
So:
b=x/y
x=y*d
Now go look up x, y, and d
Now, look up the calculations that go into x, y, and d and find the source
for each of them.
Now, substitute the original values in your formula to list out all the
pieces of the formula, along with the procedural logic which determines
which calculations to use. Not to mention the variables used in
if/then/else statements which control whether or not these values even get
set.
Congratulations, you have just recreated the original code.
The best you can do is go through the code in a good text editor with find
functions and try to figure it out. Make sure you pay particular attention
to what is going on within any nested ifs, cursors, etc. Any automated
script for this will give you the wrong formula every time.
"bradsalmon" <bradsalmon@.yahoo.com> wrote in message
news:1147183833.980625.52860@.j73g2000cwa.googlegroups.com...
> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>

Interpreting WaitResource on SysProcesses

I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
The SPID at the head of the blocking chain = 226. SPID 226 has the following
from SYSPROCESSES:
Open_Tran = 2
WaitResource = 40:1:41356
WaitType = 0x0000
cmd = INSERT
LastWaitType = PAGELATCH_SH
The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:
Open_Tran = 2
WaitResource = TAB: 40:514100872
WaitType = 0x0008
cmd = INSERT
LastWaitType = LCK_M_IX
From the above, do I interpret:
The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
The WaitResource on SPID 394 to be [DBID:OBJECTID]?
If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in this
case the locked object?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Does anyone want to take a stab at this?
cbrichards wrote:
>I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
>The SPID at the head of the blocking chain = 226. SPID 226 has the following
>from SYSPROCESSES:
>Open_Tran = 2
>WaitResource = 40:1:41356
>WaitType = 0x0000
>cmd = INSERT
>LastWaitType = PAGELATCH_SH
>The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:
>Open_Tran = 2
>WaitResource = TAB: 40:514100872
>WaitType = 0x0008
>cmd = INSERT
>LastWaitType = LCK_M_IX
>From the above, do I interpret:
>The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
>The WaitResource on SPID 394 to be [DBID:OBJECTID]?
>If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in this
>case the locked object?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1

Interpreting WaitResource on SysProcesses

I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
The SPID at the head of the blocking chain = 226. SPID 226 has the following
from SYSPROCESSES:
Open_Tran = 2
WaitResource = 40:1:41356
WaitType = 0x0000
cmd = INSERT
LastWaitType = PAGELATCH_SH
The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES
:
Open_Tran = 2
WaitResource = TAB: 40:514100872
WaitType = 0x0008
cmd = INSERT
LastWaitType = LCK_M_IX
From the above, do I interpret:
The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
The WaitResource on SPID 394 to be [DBID:OBJECTID]?
If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in thi
s
case the locked object?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Does anyone want to take a stab at this?
cbrichards wrote:
>I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
>The SPID at the head of the blocking chain = 226. SPID 226 has the followin
g
>from SYSPROCESSES:
>Open_Tran = 2
>WaitResource = 40:1:41356
>WaitType = 0x0000
>cmd = INSERT
>LastWaitType = PAGELATCH_SH
>The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSE
S:
>Open_Tran = 2
>WaitResource = TAB: 40:514100872
>WaitType = 0x0008
>cmd = INSERT
>LastWaitType = LCK_M_IX
>From the above, do I interpret:
>The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
>The WaitResource on SPID 394 to be [DBID:OBJECTID]?
>If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in th
is
>case the locked object?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1

Interpreting WaitResource on SysProcesses

I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
The SPID at the head of the blocking chain = 226. SPID 226 has the following
from SYSPROCESSES:
Open_Tran = 2
WaitResource = 40:1:41356
WaitType = 0x0000
cmd = INSERT
LastWaitType = PAGELATCH_SH
The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:
Open_Tran = 2
WaitResource = TAB: 40:514100872
WaitType = 0x0008
cmd = INSERT
LastWaitType = LCK_M_IX
From the above, do I interpret:
The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
The WaitResource on SPID 394 to be [DBID:OBJECTID]?
If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in this
case the locked object?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
Does anyone want to take a stab at this?
cbrichards wrote:
>I have a blocking issue, running SQL 2000, SP4, on Windows 2003.
>The SPID at the head of the blocking chain = 226. SPID 226 has the following
>from SYSPROCESSES:
>Open_Tran = 2
>WaitResource = 40:1:41356
>WaitType = 0x0000
>cmd = INSERT
>LastWaitType = PAGELATCH_SH
>The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:
>Open_Tran = 2
>WaitResource = TAB: 40:514100872
>WaitType = 0x0008
>cmd = INSERT
>LastWaitType = LCK_M_IX
>From the above, do I interpret:
>The WaitResource on SPID 226 to be [DBID:INDEXID:PAGEID]?
>The WaitResource on SPID 394 to be [DBID:OBJECTID]?
>If the WaitResource on SPID 394 is [DBID:OBJECTID], is the object in this
>case the locked object?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1