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 you’ll need a tool like ApexSQL Refactor – a free 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, you can use the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views.

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:

Resulting table

And with this information, you can write code that will count the number of parameter occurrences for each parameter in the object definition. In the following code we 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 your 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 your 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. You can 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 you’re writing code currently displayed in the Query editor tab

Using this free add-in for SQL Server Management Studio and Visual Studio is a great way to clean up your sql

March 9, 2015