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 a dynamic SQL and the other scenario: if we have used sp_executesql or Exec / Execute and commented it inside a Stored procedure ,
but still the above scripts will 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.