Clean up SQL code from variables you don’t use

Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things that the life make easier is to have clean code – without unnecessary and unused parameters and values.

Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup, assuming it can afford the luxury of downtime.

How to make sure the parameters/variables are not used in code

To find unused parameters in stored procedures and functions, the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views can be used. They store the parameters and names of the stored procedures and functions that were used. To list all routines and all parameters, use this code:

SELECT ROUTINE_NAME,
       P.PARAMETER_NAME
FROM   INFORMATION_SCHEMA.ROUTINES AS R 
       INNER JOIN
       INFORMATION_SCHEMA.PARAMETERS AS P
       ON P.SPECIFIC_NAME = R.ROUTINE_NAME;

With this information, the code that will count the number of parameter occurrences for each parameter in the object definition can be written. If it is equal to 1, the parameter has only been used once, i.e. only defined and not used afterwards.

The approach above, however, cannot be used for the code which is currently typed, and is not saved as a stored procedure or function.

ApexSQL Refactor is a SQL Server management Studio and Visual Studio add-in that finds unused variables and parameters and cleans up the code by identifying and removing parameters and variables that aren’t used. It can also format SQL queries, refactor SQL code, database tables and much more.

To find unused parameters:

1. Open or input code that needs to be checked in the current query editor
2. In the SQL Server Management Studio / Visual Studio menus, select the Unused variables and parameters command from the ApexSQL Refactor menu:
   

After the process of finding unused parameters and variables is finished, the unused parameters will be underlined. Mouse-over on them to see a tooltip that clarifies the parameter/variable:

Finding the parameters and variables that are only declared, but not actually used in code can be quite easy. Use ApexSQL Refactor to clean up the code and remove unnecessary bloat.