sql code check

Last Post 29 Jul 2010 08:07 AM by russellb. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dbandee
New Member
New Member

--
29 Jul 2010 07:47 AM
Hi i inherited a system which i am supporting now.
it is 2005 i found a few sprocs where cursor is not explicitly closed nor deallocated.
this proc is called 100s time a day by diff procs;
i fixed it by adding close and deallocate current system has about 800 sprocs and 1500 functions
My question is: is there a tool to check all sprocs for such thing as cursor being left open? thanks
russellb
New Member
New Member

--
29 Jul 2010 08:07 AM
This will get you started:

select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%CURSOR%'
And ROUTINE_DEFINITION NOT LIKE '%DEALLOCATE%'

you'll need to handle any SPs that are more than 4000 characters. Also, it won't catch it if the DEALLOCATE is commented out.
You are not authorized to post a reply.

Acceptable Use Policy