Parsing out complete dynamic SQL expressions from hundreds of stored procedures
<p>I've inherited a large application that has many hundreds of stored procedures, many of which use dynamic SQL. In order to get a better handle of the types of SQL I am dealing with, it would be extremely useful if I had a way to parse the query text of <em>all</em> these stored procedures and extract the complete expression for any dynamic SQL contained within.</p>

<p>A simplified expression might be:</p>

<pre><code>declare @query nvarchar(max)
set @query = 'SELECT col1,col2,col3 from ' + @DatabaseName + '.dbo.' + @TableName + ' WHERE {some criteria expression that also contains inline quotes}'
</code></pre>

<p>The output I am seeking for the above (that would ultimately be called in a single query that parses all stored procedures) is:</p>

<pre><code>SELECT col1, col2, col3 
FROM ' + @DatabaseName + '.dbo.' + @TableName + ' 
WHERE {some criteria expression that also contains inline quotes}
</code></pre>

<p>So, not the expression after the parameter values have been passed in, but the expression text as in the stored procedure text, including the parameter names.</p>

<p>I'm ok with the not-at-all-safe assumption that the dynamic SQL parameter name is <code>@query</code>, so searching for this within the SQL expression to use as a starting position to extract text would be tolerable, but since there are single quotes inline, I have no easy way of knowing where the assignment to the variable is complete.</p>

<p>I'm including the [antlr] and [parsing] tags in this question because I have a feeling this is beyond what's capable in T-SQL.</p>

<p>PS:  Yes, I'm well aware "I shouldn't be doing this".</p>

<p><strong>EDIT</strong></p>

<p>From a suggestion below, tried the following query but not really useful in this context:</p>

<pre><code>SELECT 
db_name(dbid) DB_NAME
,cacheobjtype, objtype, object_name(objectid) ObjectName
,objectid 
,x.text
,usecounts 
--  , x.*,z.* ,db_name(dbid)
FROM 
sys.dm_exec_cached_plans z
CROSS APPLY sys.dm_exec_sql_text(plan_handle)  x
WHERE 
    --usecounts &gt; 1 
    --objType = 'Proc' and  -- include if you only want to see stored procedures 
    db_name(dbid) not like 'ReportServer%' and db_name(dbid) &lt;&gt; 'msdb' and db_name(dbid) not like 'DBADB%' and db_name(dbid) &lt;&gt; 'master'
--ORDER BY usecounts DESC
ORDER BY objtype
</code></pre>