tools/README_FIND_DYNAMIC_SQL.md

Find dynamic SQL in stored procedures

Files

• find_dynamic_sql_procs.sql: parameterized T-SQL scanner. Use in SSMS, Azure Data Studio, or sqlcmd.
• find_dynamic_sql_procs.py: small CLI wrapper that runs the SQL and emits CSV.

Quick examples

1. Run the SQL directly (Azure Data Studio/SSMS):

   -- edit variables at top of file or use client substitution :setvar TopN 200 :setvar ContextChars 120

   -- open and run the file

2. Run via python wrapper (recommended for automation):

python tools/find_dynamic_sql_procs.py --server demo.azonix.in --database hs-prod3 --uid myuser --pwd "mypassword" --top 200 > dynamic_procs.csv

Notes & limitations

• The scanner uses simple LIKE patterns to detect likely dynamic SQL usage (sp_executesql, EXEC(@var), string concatenation).
• It prioritizes precision via a small confidence score but will miss obfuscated dynamic SQL and may return false positives from comments or similar tokens.
• Combine the output with manual inspection of definition for highest accuracy.
• The Python wrapper requires pyodbc and an appropriate ODBC driver (e.g. ODBC Driver 17/18 for SQL Server).

Security

• Avoid putting credentials in shell history. Prefer using a secure credential store or trusted connections.

Next steps (optional)

• Add a mode to export full proc definitions to files.
• Add a deeper parser using tSQLt or ANTLR for T-SQL to more accurately detect dynamic SQL boundaries.