T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL
This script was developed to answer the question in this thread: I need query find all the SPs that used dynamic SQL
We can execute dynamic SQL using sp_executesql or just with Exec / Execute.
To find the names of the StoredProcedure that may have used dynamic SQL, this script can be used:
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')
But Exec / Execute can be used inside a stored procedure to call another stored procedure or to execute a dynamic SQL. So, to eliminate the stored procedure names referencing another stored procedure and to find the names of the stored procedure that has used Exec / Execute only to execute dynamic SQL,
the following script can be used:
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')
EXCEPT
SELECT StoredProcedure FROM (
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProcedure
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')) tmp
CROSS APPLY sys.dm_sql_referenced_entities (StoredProcedure, 'OBJECT');
The above script will not work under the following scenarios:
- if we have used Exec / Execute inside a stored procedure for both purposes, i.e. to call another stored procedure and to execute dynamic SQL.
- if we have used sp_executesql or Exec / Execute and commented it inside a stored procedure.
The above scripts will still be useful because we don't have any other direct way to find the names of the stored procedure that has used dynamic SQL.
This script also won't work for encrypted procedures.