-- tools/find_dynamic_sql_procs.sql
-- Parameterized T-SQL to scan all stored procedure sources for dynamic-SQL patterns.
-- Usage:
--   :setvar SearchPattern "sp_executesql|EXEC\s*\(|EXEC\s+@|\+\s*'"
--   :setvar TopN 1000
--   :setvar ContextChars 120
-- Run in SSMS, Azure Data Studio or sqlcmd (sqlcmd: use -v to pass variables).

SET NOCOUNT ON;

-- variables via sqlcmd or client substitution
DECLARE @SearchPattern NVARCHAR(400) = 'sp_executesql|EXEC\s*\(|EXEC\s+@|\+\s*\''; -- regex-like tokens (plain LIKE uses %)
DECLARE @Top INT = 1000;
DECLARE @Context INT = 120; -- characters of surrounding context

-- We use simple LIKE checks plus more specific patterns to score confidence.

SELECT TOP(@Top)
    s.name AS schema_name,
    p.name AS proc_name,
    p.create_date,
    p.modify_date,
    m.definition,
    -- basic token flags
    CASE WHEN m.definition LIKE '%sp_executesql%' THEN 1 ELSE 0 END AS has_sp_executesql,
    CASE WHEN m.definition LIKE '%EXEC %(%' ESCAPE '\' THEN 1 ELSE 0 END AS has_exec_parenthesis,
    CASE WHEN m.definition LIKE '%EXEC @%' THEN 1 ELSE 0 END AS has_exec_variable,
    CASE WHEN m.definition LIKE '%''+%''%' ESCAPE '\' OR m.definition LIKE '%'' + %' ESCAPE '\' THEN 1 ELSE 0 END AS has_string_concat,
    -- rough confidence score
    (CASE WHEN m.definition LIKE '%sp_executesql%' THEN 3 ELSE 0 END
     + CASE WHEN m.definition LIKE '%EXEC %(%' ESCAPE '\' THEN 2 ELSE 0 END
     + CASE WHEN m.definition LIKE '%EXEC @%' THEN 2 ELSE 0 END
     + CASE WHEN m.definition LIKE '%''+%''%' ESCAPE '\' OR m.definition LIKE '%'' + %' ESCAPE '\' THEN 1 ELSE 0 END) AS confidence_score
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE m.definition IS NOT NULL
  AND (
        m.definition LIKE '%sp_executesql%'
     OR m.definition LIKE '%EXEC %(%' ESCAPE '\'
     OR m.definition LIKE '%EXEC @%'
     OR m.definition LIKE '%''+%''%' ESCAPE '\'
     OR m.definition LIKE '%'' + %' ESCAPE '\'
  )
ORDER BY confidence_score DESC, s.name, p.name;