Find all variables in SQL script
It’s quite easy to use General SQL Parser to find out all SQL variables in a SQL script. It will not only find variables in declare statement but also find variables where it was used.
This is a query includes SQL variables
DECLARE @ProductId BIGINT
DECLARE @OTSProductId BIGINT
SET @ProductId = 35768
SET @OTSProductId = @ProductId
IF NOT EXISTS(SELECT ProductId
FROM PRODUCT
WHERE PRODUCTID = @ProductId)
BEGIN
INSERT INTO [Product]
([ProductId],
[Name])
SELECT @ProductId,
'Please see the insert in this bill ...'
END
ELSE
BEGIN
PRINT REPLACE('product already exists', '', @ProductId)
END
All SQL varaibles found in that SQL script
@ProductId @OTSProductId @ProductId @OTSProductId @ProductId @ProductId @ProductId @ProductId
Even more, General SQL Parser can find variable used throughout the whole stored procedure to extract a lineage.
Take this SQL for example
CREATE PROCEDURE STGTRSODS.P_LOAD_DC_UNIVL_PLAN_R2_R3
( IN in_cycle_id CHAR(2))
RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
------------------------------------------------------------------------
-- DECLARE SECTION
------------------------------------------------------------------------
DECLARE SQLSTATE CHAR(5);
DECLARE wk_sqlstate CHAR(5) DEFAULT '00000';
DECLARE cs_VALUE CHARACTER(100);
DECLARE wk_fetch_status CHAR(5) DEFAULT '00000';
DECLARE cs_PLAN_NUM CHARACTER(6);
DECLARE cs_SUB_PLAN CHARACTER(6);
--CLARE cs_if_cursor CURSOR WITH HOLD WITH RETURN FOR cs_if_cursor;
SET wk_stg_cfe_r3 = ACCTLOAD.F_GET_ETL_TABLE(in_cycle_id,'ACCTLOAD.T_STG_CFE_$$$_R3');
SET wk_stg_cfe_r2 = ACCTLOAD.F_GET_ETL_TABLE(in_cycle_id,'ACCTLOAD.T_STG_CFE_$$$_R2');
FETCH cs_if_cursor
INTO cs_PLAN_NUM, cs_SUB_PLAN, cs_KEY_WORD, cs_VALUE;
WHILE (wk_fetch_status = '00000') DO
CASE cs_KEY_WORD
WHEN 'DSHR PARTICIPATING DATASHARE INDICATOR' THEN
IF EXISTS(SELECT 1 FROM SESSION.GLBLTMP_R3_R2 WHERE PLAN_NUM = cs_PLAN_NUM AND SUB_PLAN = cs_SUB_PLAN) THEN
UPDATE SESSION.GLBLTMP_R3_R2
SET DSHR_PARTICIPATING_IND = SUBSTR(cs_VALUE,1,1)
WHERE PLAN_NUM = cs_PLAN_NUM
AND SUB_PLAN = cs_SUB_PLAN;
ELSE
INSERT INTO SESSION.GLBLTMP_R3_R2 (PLAN_NUM,SUB_PLAN, DSHR_PARTICIPATING_IND, DSHR_FUNCTION)
VALUES (cs_PLAN_NUM,cs_SUB_PLAN, SUBSTR(cs_VALUE,1,1), NULL);
END IF;
WHEN 'DSHR DATASHARE FUNCTION' THEN
IF EXISTS(SELECT 1 FROM SESSION.GLBLTMP_R3_R2 WHERE PLAN_NUM = cs_PLAN_NUM AND SUB_PLAN = cs_SUB_PLAN) THEN
UPDATE SESSION.GLBLTMP_R3_R2
SET DSHR_FUNCTION = SUBSTR(cs_VALUE,1,1)
WHERE PLAN_NUM = cs_PLAN_NUM
AND SUB_PLAN = cs_SUB_PLAN;
ELSE
INSERT INTO SESSION.GLBLTMP_R3_R2 (PLAN_NUM,SUB_PLAN, DSHR_PARTICIPATING_IND, DSHR_FUNCTION)
VALUES (cs_PLAN_NUM,cs_SUB_PLAN, NULL, SUBSTR(cs_VALUE,1,1));
END IF;
END CASE;
END WHILE;
END P1
Analyzed result will like this:
procedure name: STGTRSODS.P_LOAD_DC_UNIVL_PLAN_R2_R3 Variable: wk_sqlstate , Type: CHAR(5) Variable: cs_VALUE , Type: CHARACTER(100) Variable: wk_fetch_status , Type: CHAR(5) Variable: cs_PLAN_NUM , Type: CHARACTER(6) Variable: cs_SUB_PLAN , Type: CHARACTER(6) variable: cs_VALUE update table: SESSION.GLBLTMP_R3_R2 column: DSHR_PARTICIPATING_IND value: SUBSTR(cs_VALUE,1,1) variable: cs_PLAN_NUM insert table: SESSION.GLBLTMP_R3_R2 column: PLAN_NUM insert value: cs_PLAN_NUM variable: cs_SUB_PLAN insert table: SESSION.GLBLTMP_R3_R2 column: SUB_PLAN insert value: cs_SUB_PLAN variable: cs_VALUE insert table: SESSION.GLBLTMP_R3_R2 column: DSHR_PARTICIPATING_IND insert value: SUBSTR(cs_VALUE,1,1) variable: cs_VALUE update table: SESSION.GLBLTMP_R3_R2 column: DSHR_FUNCTION value: SUBSTR(cs_VALUE,1,1) variable: cs_PLAN_NUM insert table: SESSION.GLBLTMP_R3_R2 column: PLAN_NUM insert value: cs_PLAN_NUM variable: cs_SUB_PLAN insert table: SESSION.GLBLTMP_R3_R2 column: SUB_PLAN insert value: cs_SUB_PLAN variable: cs_VALUE insert table: SESSION.GLBLTMP_R3_R2 column: DSHR_FUNCTION insert value: SUBSTR(cs_VALUE,1,1)
Download this demo: C# version