Unused variable detection in SQL Server

Question

Is there a way to identify local variables that have been created (or even created and set to a value), but are never used again? I'm thinking along the lines of when an IDE will underline such variables with a squiggly line.

If this isn't something that can be enabled within SSMS, are there any add-ins that do this?

(Particularly interested in SQL Server 2008 R2, if it matters)

Thanks for the insight.

Answer 1

SSMS doesn't do that, unfortunately. At all. Which surprises me in a way, because it can't be that hard to determine.

I don't know about add-ins, but Mr. Smith had a suggestion in his comment.

Answer 2

ApexSQL Refactor, a free SSMS and VS SQL formatting and refactoring add-in, has a feature that finds unused variables and parameters and cleans up your code by identifying and removing parameters and variables that aren’t used.

You can find more info about this feature in this article

Disclaimer: I work for ApexSQL as a Support Engineer

Answer 3

you can try SqlCodeGuard from http://www.sqlcodeguard.com/ - free addin for SSMS

it has a bunch of t-sql code checks and have rule "MI005 Variable is declared but never used"