How to identify procedures with dynamic sql?

Question

Post reply

How to identify procedures with dynamic sql?

WayneS

SSC Guru

Points: 95460
More actions
August 3, 2010 at 9:15 am

#225645

I'm trying to determine all the procedures that utilize dynamic sql.
The only way I've come up with is:
select sasm.*
from sys.all_sql_modules sasm
JOIN sys.objects so
ON so.object_id = sasm.object_id
where sasm.definition like '%sp[_]executesql%'
and so.is_ms_shipped = 0
However, this only gets code that uses sp_executesql.
I'd like to find procedures that use exec (), execute (), etc.
The problem with this is that there may be 0-n spaces between the keyword and the parenthesis.
If I just look for "exec", I can get all procedures that just call another procedure.
So, does anyone have a good method to identify all procedures that are using dynamic sql?
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

can't you ignore the second bracket in your search?

alternatively search for

exec('

and

exec(@[a-Z]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

This is slow but it works ...

select sasm.*

from sys.all_sql_modules sasm

JOIN sys.objects so

ON so.object_id = sasm.object_id

where (replace(sasm.definition, ' ', '') like '%exec(%'

or replace(sasm.definition, ' ', '') like '%execute(%')

and so.is_ms_shipped = 0

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 3

bt, this looks promising. I don't really care about the speed - it's a one-off thing.

I just need to identify all the code with dynamic sql.

I'll try it out tomorrow, and let post back with how it works.

(Just what I love about this site. I was so focused on identifying it one way with multiple spaces, and I never even considered yanking out all the spaces.)

Thanks!!!!!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2