How do identify stored procedure containing dynamic sql

Question

What is the best way to identify stored procedures that contains dynamic SQL? I created this SQL statement:

SELECT * 
FROM sys.sql_modules  
WHERE 
    REPLACE(definition,' ','') LIKE '%exec(%' 
    OR definition LIKE '%sp_executesql%' 
    OR REPLACE(definition,' ','') LIKE '%execute(%'

Will it find all instances?

Answer 1

I cannot provide you with exact examples, but I can probably point you into right directions. There are Microsoft's classes that are responsible for parsing and generation of SQL batches. You can probably use those classes to parse content of stored procedures and look for all possible real dynamic sql invocations.

More information here: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.transactsql.scriptdom(v=sql.110).aspx

I am not sure, whether this covers SQL 2008 as well. Sorry about that.

Here's a sample of how to use those classes to remove comments in SQL scripts:

http://michaeljswart.com/2014/04/removing-comments-from-sql/

EDIT

There's and older, also Microsoft's solution, to parse SQL statements. Not as powerful, but SQL 2008 compatible. Please check whether this could suit your needs:

https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser(v=sql.105).aspx