How to clean up SQL database code by removing unused parameters

Finding parameters and SQL variables that are only defined in the existing SQL Server stored procedures and functions, but never actually used, is not a problem, but for maintaining complex code, with dozens of parameters and variables, a tool like ApexSQL Refactor can be used, which is a SQL Server Management Studio and Visual Studio add-in, and useful SQL query formatter.

If a parameter or variable has been declared or assigned a value, but never used or queried in any statements like INSERT, UPDATE, EXECUTE, PRINT, or WHERE clauses, etc., it is considered unused.

To find unused parameters in stored procedures and functions, the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views can be used.

To list all routines and all parameters:

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

Resulting in:

And with this information, a code that will count the number of parameter occurrences for each parameter in the object definition can be written. The following code will list only objects that contain unused parameters:

SELECT 
ROUTINE_NAME AS ObjectName, 
PARAMETER_NAME AS ParameterName 
FROM AdventureWorks2008R2.INFORMATION_SCHEMA.ROUTINES 
INNER JOIN
AdventureWorks2008R2.INFORMATION_SCHEMA.PARAMETERS 
ON PARAMETERS.SPECIFIC_NAME = ROUTINES.ROUTINE_NAME
WHERE
(LEN(OBJECT_DEFINITION(OBJECT_ID(ROUTINES.ROUTINE_NAME)) ) - 
LEN(REPLACE(OBJECT_DEFINITION(OBJECT_ID(ROUTINES.ROUTINE_NAME)) , PARAMETERS.PARAMETER_NAME, ''))
- LEN(PARAMETERS.PARAMETER_NAME))= 0
AND LEN(PARAMETERS.PARAMETER_NAME)<> 0

Which gives us the result for a specified database:

A downside of using a SQL query is that it will only show the unused parameters after a stored procedure has been compiled.

ApexSQL Refactor can clean up SQL database code by analyzing and automatically highlighting the superfluous SQL objects. This is especially useful, for example, if you have a legacy database and you want to know if SQL objects are referencing other SQL objects that no longer exist in the SQL database and remove unnecessary bloat.

To find unused variables and parameters in a SQL database code with ApexSQL Refactor, simply copy it in the SQL query editor.

From the ApexSQL Refactor menu select the Unused variables and parameters feature:

Once the search is over all unused parameters and variables are highlighted. Mouse-over them to see a tooltip that clarifies the parameter/variable:

The main advantage of using ApexSQL Refactor is that can be used to find unused parameters and variables inline, while the code is displayed in the Query editor tab.

Using this add-in for SQL Server Management Studio and Visual Studio is a great way to clean up SQL code.