April 21, 2014 at 7:46 am
Hi Experts,
How to Check the stored procedures to find redundant calls within it?
Thanks in Advance.
April 21, 2014 at 11:42 am
since redundancy type issues are logic based, i think you are stuck with actually reviewing the code. digging through the logic is a pain.
If you can give a better example of what you specifically think of as redundant calls, we could help better.
one example i can think of that I think of redundancy:
unnecessary updates for example, UPDATE IsActiveFlag = 1 WHERE SomeCriteria = x
it should really be UPDATE IsActiveFlag = 1 WHERE SomeCriteria = x AND IsActiveFlag = 0 for example.
Lowell
April 22, 2014 at 4:53 am
Thanks Lowell for the reply.
I am looking for procedures calling multiple same procedure inside.
April 22, 2014 at 5:17 am
Ratheesh.K.Nair (4/22/2014)
Thanks Lowell for the reply.I am looking for procedures calling multiple same procedure inside.
u mean SP calling itself ?
April 22, 2014 at 6:00 am
that's still a logical decision; from a static code point of vew, you could check if any procedure had a cursor: that would imply that it is calling some code multiple times.
select * from sys.sql_modules where definition like '%CURSOR%'
you could use either sys.sql_expression_dependencies or a join to find procs that reference other procedures; and just check the code of them.
SELECT
depz.referenced_schema_name,
depz.referenced_entity_name,
objz.type_desc,
object_schema_name(depz.referencing_id),
object_schema_name(depz.referencing_id),
object_name(depz.referencing_id),
colz.name AS ColumnName
FROM sys.sql_expression_dependencies depz
INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referencing_minor_id
select
row_number() OVER (Partition BY procz.name order by procz.name) AS RW ,
object_name(modz.object_id) As ObjectName,
--definition,
procz.name
from sys.sql_modules modz
INNER JOIN sys.procedures procz ON modz.DEFINITION LIKE '%' + procz.name + '%'
WHERE object_name(modz.object_id) <> procz.name
GROUP BY object_name(modz.object_id),procz.name
you could split the definition in sys.sql_modules by whitespace, and find anythign that has the same procedure name more than once.
this was brutally slow on my dev server, because there's so much data int he definitions to split; i also had to use a varchar(max) version of DelimitedSplit8K
SELECT * FROM (
select
row_number() OVER (Partition BY procz.name order by procz.name) AS RW ,
modz.name As ObjectName,
definition,
procz.name
from (select
object_name(modz.object_id) AS name,
definition,
FN.*
from sys.sql_modules modz
CROSS APPLY dbo.DelimitedSplit8K(definition,' ') FN ) modz
INNER JOIN sys.procedures procz ON modz.Item LIKE '%' + procz.name + '%'
WHERE modz.name <> procz.name )
X
WHERE RW > 1
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply