find out SQL scripts that contain cursor, while loop

Dear all,

from time to time we suggest programmer to not use cursor and while loop, but can we find out by ourselves how many still left on coding including application tier ?

I want to find out and show the developer there are still some of them not change/convert to set based yet !
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You can search for a term in Stored Procedures using this query:
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS 
WHERE ([text] LIKE '%CURSOR%' Or [text] LIKE '%WHILE%') AND
      OBJECTPROPERTY(id, 'IsProcedure') = 1 

Open in new window

You can use different OBJECTPROPERTY values, such as 'IsTrigger' to check other object types
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
Forgot to mention. To search for the CURSOR or WHILE in the text coming from the application queries you will need to use the SQL Server profiler. You will need to start the profiler, create a new filter and setup the conditions in the filter to match for the application name (your application name), the query terms (CURSOR, WHILE) and start the profiling. Then you will need to click every button in your application and watch the profiler window
1
marrowyungSenior Technical architecture (Data)Author Commented:
"You will need to start the profiler, create a new filter and setup the conditions in the filter to match for the application name (your application name), the query terms (CURSOR, WHILE) and start the profiling"

any step by step instruction on this ?

so this one can help to check out which server this script store and what is the component, SQL jobs calls this ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chaauCommented:
There is one here. It provides with the basic steps how to create a new trace. You will have to fine tune  the trace by adding some filters to exclude the system processes, to exclude the other applications, to narrow down to a single database, etc. These are all done via the Trace properties. Try it, it is very easy to use. In the Events window I recommend you choose the TSQL->SQL:BatchCompleted event. There, you can filter it even further by restricting it by the Application Name, and SQL Text. The filter is defined via the "Column Filter" button. You can then save the trace to reuse it later
0
marrowyungSenior Technical architecture (Data)Author Commented:
so if I want to find out all query rans which is slow and what needs to be done, I should choose the TSQL->SQL:BatchCompleted event ?  then the result then import to DTA ?


what is the best option to trace so that DTA can have a complete picture to give suggestion including index?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wouldn't mind if they are using it. Usually a DBA have plenty of things to worry about and mostly don't have time to be proactive.
I would put my attention and energy in finding long running queries and trying to improve their performance. Some of them might use cursors but some not so it's not an 100% rule to follow.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I wouldn't mind if they are using it. Usually a DBA have plenty of things to worry about and mostly don't have time to be proactive."

yeah you are right? usually I prefer to buy a good monitoring tools to monitor the SQL server and I just have to give a access account for the monitoring tools to connect to any of our SQL server, instead of creating them one by one.

" Some of them might use cursors but some not so it's not an 100% rule to follow.

if WE can get ride of all while loop and cursor to set-based query, then our DB performance is quite good already.

this kind of RBAR way is very common.
0
marrowyungSenior Technical architecture (Data)Author Commented:
will come back for this question if I still have question, I am no time to test this now.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
if WE can get ride of all while loop and cursor to set-based query, then our DB performance is quite good already.
Once again the answer is: DEPENDS.
Depends if the alternative is a better solution or not. I also like to avoid cursors as much as I can but already had to chose the cursor solution because alternatives were even worse performance.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I also like to avoid cursors as much as I can but already had to chose the cursor solution because alternatives were even worse performance. "

you mean when compare to while loop ? :):)
 
take a look at this:

http://www.codeproject.com/Articles/228814/Replacing-Cursors-with-Set-Based-SQL-Queries-Part

replace while/cursor + IF else statement with CASE and set-based seems a good direction to go.
0
marrowyungSenior Technical architecture (Data)Author Commented:
chaau,

for your script:

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS 
WHERE ([text] LIKE '%CURSOR%' Or [text] LIKE '%WHILE%') AND
      OBJECTPROPERTY(id, 'IsProcedure') = 1 

Open in new window


what can I do to check for a particular database ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.