Finding the invalid views like in conditions where underlying table/column deleted

Database
Enthusiast

Finding the invalid views like in conditions where underlying table/column deleted

Hi Forum,

 

I am trying to add the continue handler on stored procedure which will find the invalid views, like if underlying table/column deleted, then help view return 3807 sql code and sql state 42000,at this point i need to further process the cursor and insert the failed view name onto some table. I tried below with no success, Any ideas/inputs to below stored procedure?

 

create procedure findinvalidview()
begin
declare sqltxt varchar(100);
	for cur as select databasename as dbname,tablename as tbname from dbc.tables where tablekind='V' and databasename='TEMP';
	set sqltxt='help view '||cur.dbname||'.'||cur.tbname||';';  
	execute immediate sqltxt;
		if sqlcode = 3807 OR sqlstate = 42000
		then insert into invalidviewlist(cur.dbname,cur.tbname,:sqlstate);
		else donothing;
		end if;
    end for;
end

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

1 REPLY 1
Highlighted
Teradata Employee

Re: Finding the invalid views like in conditions where underlying table/column deleted

You can't use HELP in a stored procedure (and even if you could, note that as a data-returning statement it would require a cursor and would not be valid for EXECUTE IMMEDIATE). 

Maybe try issuing a PREPARE of a "SELECT * FROM viewname;" statement instead.