SQL Server : Script for finding which variable is declared but never used

Hi,

Below query will help to get the list of all the variable which are declared in your sql code but never used.


-- Variable declared but never used Script
-- Original Author : Rahul Singi
DECLARE @str AS VARCHAR(MAX)=''
DECLARE @chrFound AS VARCHAR(255)
DECLARE @i AS INT=0
DECLARE @temp AS TABLE(colm1 VARCHAR(255))

SET @str=LOWER(@str)

DECLARE @NewLine AS CHAR(2)

SET @NewLine=char(13)+char(10)

WHILE @i<len(@str)
BEGIN
    IF((CHARINDEX('@',@str,@i)=0) OR (CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))-CHARINDEX('@',@str,@i))<0)
    BEGIN
        BREAK;
    END
    
    SELECT @chrFound=SUBSTRING(@str,CHARINDEX('@',@str,@i),CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))-CHARINDEX('@',@str,@i))
    
    INSERT INTO @temp
    SELECT rtrim(ltrim(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@chrFound,'!',''),'#',''),'$',''),'&',''),'=',''),')',''),';',''),@NewLine,'')))
    WHERE @chrFound NOT IN ('@@ERROR','@@IDENTITY','@@PACK_RECEIVED','@@ROWCOUNT','@@TRANCOUNT')
    
    SET @i=CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))
    
    IF(CHARINDEX('@',@str,@i)=0)
    BEGIN
        BREAK;
    END
    
    PRINT(@i)
END

UPDATE @temp SET colm1=substring(colm1,0,charindex(char(9),colm1,0))
WHERE charindex(char(9),colm1,0)>0

SELECT colm1,'Variable declared but never used' AS Comment
FROM @temp
WHERE colm1 NOT IN(SELECT colm1
                   FROM @temp
                   GROUP BY colm1
                   HAVING COUNT(colm1)>1)



In the @str variable you need to give your sql code

Thx,
RS