Receiving error while activating procedure - Dependent object not found

[Former Member]

Scenario: To update the comments for the columns for all tables in a list of schemas automatically via procedure

Here's the code:

PROCEDURE "DEBANMUKHERJEE"."PROOF_OF_CONCEPTS.DEBANSHU_POC.PROCEDURES::SP_POPULATE_COMMENTS" ( ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER --DEFAULT SCHEMA <default_schema_name> AS BEGIN DECLARE V_SCHEMA_LOOP INTEGER := 0; DECLARE V_TABLE_LOOP INTEGER := 0; DECLARE V_COLUMN_LOOP INTEGER := 0; DECLARE V_SCHEMA_NAME NVARCHAR(50); DECLARE V_TABLE_NAME NVARCHAR(50); DECLARE v_COLUMN_NAME NVARCHAR(50); DECLARE V_COMMENTS NVARCHAR(100); SELECT COUNT(*) INTO V_LOOP_SCHEMAS FROM "_SYS_BIC"."PROOF_OF_CONCEPTS.ADI_POC/CV_CONS_SAP_ALL_SCHEMAS_DIM"; IF V_LOOP_SCHEMAS = 0 THEN SELECT 'NO SCHEMA TO READ' AS "WARNING" FROM DUMMY; ELSE V_SCHEMAS = SELECT DISTINCT "SCHEMA_NAME" FROM "_SYS_BIC"."PROOF_OF_CONCEPTS.ADI_POC/CV_CONS_SAP_ALL_SCHEMAS_DIM"; WHILE V_LOOP_SCHEMAS > 0 DO SELECT TOP 1 SCHEMA_NAME INTO V_SCHEMA_NAME FROM :V_SCHEMAS ; SELECT COUNT(DISTINCT TABLE_NAME) INTO V_LOOP_TABLES FROM SYS.M_TABLES WHERE SCHEMA_NAME = :V_SCHEMA_NAME; IF V_LOOP_TABLES = 0 THEN SELECT 'INPUT SCHEMA IS EMPTY' AS "WARNING" FROM DUMMY; ELSE V_TABLES = SELECT DISTINCT "TABLE_NAME" FROM SYS.M_TABLES WHERE SCHEMA_NAME = :V_SCHEMA_NAME ; WHILE V_LOOP_TABLES > 0 DO SELECT TOP 1 TABLE_NAME INTO V_TABLE_NAME FROM :V_TABLES ; SELECT COUNT(DISTINCT COLUMN_NAME) INTO V_LOOP_COLUMNS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = :V_SCHEMA_NAME AND TABLE_NAME = :V_TABLE_NAME ; V_COLUMNS = SELECT DISTINCT COLUMN_NAME FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = :V_SCHEMA_NAME AND TABLE_NAME = :V_TABLE_NAME ; WHILE V_LOOP_COLUMNS > 0 DO SELECT TOP 1 COLUMN_NAME INTO V_COLUMN_NAME FROM :V_COLUMNS ; IF (SELECT COMMENTS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = :V_SCHEMA_NAME AND TABLE_NAME = :V_TABLE_NAME AND COLUMN_NAME = :V_COLUMN_NAME) IS NULL THEN SELECT "COMMENTS" INTO V_COMMENTS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = 'ZZZ' AND TABLE_NAME = :V_TABLE_NAME AND COLUMN_NAME = :V_COLUMN_NAME; EXEC 'COMMENT ON COLUMN ' || :V_SCHEMA_NAME || '.' || :V_TABLE_NAME || '.' || :V_COLUMN_NAME || ' IS ' || :V_COMMENTS ; ELSE SELECT 'COMMENT ON COLUMN ' || :V_SCHEMA_NAME || '.' || :V_TABLE_NAME || '.' || :V_COLUMN_NAME || ' ALREADY EXISTS ' FROM DUMMY; END IF; DELETE FROM V_COLUMNS WHERE COLUMN_NAME = :V_COLUMN_NAME; SELECT COUNT(*) INTO V_LOOP_COLUMNS FROM :V_COLUMNS; END WHILE; DELETE FROM V_TABLES WHERE TABLE_NAME = :V_TABLE_NAME; SELECT COUNT(*) INTO V_LOOP_TABLES FROM :V_TABLES; END WHILE; END IF ; DELETE FROM V_SCHEMAS WHERE SCHEMA_NAME = :V_SCHEMA_NAME; SELECT COUNT(*) INTO V_LOOP_SCHEMAS FROM :V_SCHEMAS; END WHILE; END IF; END;

Error received:

Dependent object not found: SqlScript; DEBANMUKHERJEE.V_COLUMNS: symbol not found

[former_member235152]

Did you resolve it???? How?

[former_member754686]

Any progress?