All Forums Database
Harpreet Singh 101 posts Joined 10/11
30 May 2013
View validity check

Hi,
How Can I check which views in particular database are invalid using system tables? Invalid means that objects referenced by view are not present now and were dropped so view will give error now saying table does not exist.
Thanks

ulrich 816 posts Joined 09/09
30 May 2013

its not possible to get this info out of dbc
it can change any time and maybe in 5 min the view is valid again.
try the following bteq script

.logon system/user,passwd;

.set width 500;
create volatile table invalid_views (databasename varchar(30), tablename varchar(30)) unique primary index (databasename, tablename) on commit preserve rows;

.OS rm /yourpath/out.bteq

.export data file=/yourpath/out.bteq
.RECORDMODE OFF
.FOLDLINE ALL

select 'show select * from ' || trim(databasename) || '.' || trim(tablename) || ';',
'.if errorcode = 0 then .goto next;',
'insert into invalid_views values ('''||trim(databasename)||''',''' ||trim(tablename)||''');',
'.label next;'
from dbc.tables 
where tablekind = 'V'
order by 1;

.export reset

.run file = /yourpath/out.bteq

.FOLDLINE OFF ALL

select * 
from invalid_views
order by 1,2
;

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Harpreet Singh 101 posts Joined 10/11
30 May 2013

Thanks Ulrich. It got me all invalid views.

You must sign in to leave a comment.