SQL Prompt can find any parameters or variables that are unused in a script.

A parameter or variable is considered to be unused if it has been declared or assigned a value, but not queried or used in any control statements, and not used in INSERT or UPDATE statements, WHERE clauses, PRINT or EXECUTE statements, loops or case statements, and so on.

To find unused variables and parameters:

1. Open the script you want to use in a SQL Server Management Studio query window.
2. If required, select the block of SQL that contains the parameters and variables that you want to check.
   If you do not select any SQL, all parameters and variables in the script are checked.
3. On the SQL Prompt menu, click Find Unused Variables and Parameters.
   Unused parameters, variables, and values are underlined in the query:
   
   
   
   SSMS 2016 and higher will also mark the locations of underlined items in the scrollbar which is helpful for longer queries.