All Forums Database
Jeanne-Anne 5 posts Joined 09/05
04 Apr 2006
Identifying invalid views?

In our development and test environments, it is very common for a view to be created and then the underlying object changed or dropped. This renders the view invalid and any action attempted with it results in one of the following errors: *** Failure 3807 Object '___' does not exist. *** Failure 3810 Column/Parameter '___' does not exist.Is there some way to query the data dictionary to find these invalid views? Having a maintenance script that tries to access every view in the database seems horribly inefficient.

Ranga-746 2 posts Joined 12/04
05 Apr 2006

There is no straight forward answer to your problemHow ever i used to generate dynamic sql statementsfor each view like sel * from view1 where 1 = 2;I used put all this in bteq script.use ''.SET ERROROUT STDOUT;" after your .log on credentials.When you run the script and open your log file you will find where ever you see those errors numbers, those are the INVALID vwsHope this helpsRanga

BBR2 96 posts Joined 12/04
24 Jun 2006

I wrote a bteq script which helps in identifying invalid views under a particular parent (eg SOUTHEASTDW in this case).It is run in windows enviornement (file/etl server) but can easily be changed to an UNIX script. I have used a global temporary table called INVALID_VIEWS but may also be a perm table (if needed).This can also be changed for capturing other types of errors.In this sample example, I have captured only 3807.Use it if you want to identify invalid views. see attachment for script.Vinay

nd7291 1 post Joined 05/09
21 Jun 2010

Hi Vinay, Can you please share this script again as attachment is not visible? Thanks in advance.

You must sign in to leave a comment.