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):

```bash
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.