How to find Invaild View which include dropped tables in defination

Database
Highlighted
Enthusiast

How to find Invaild View which include dropped tables in defination

Hi,

 

We have a requirement to find out views which include drooped tables. These views are invalid views.

Can we list out such views which include dropped tables in defination? 

 

Thanks

Tags (3)
1 REPLY
Teradata Employee

Re: How to find Invaild View which include dropped tables in defination

I would probably write a shell script to:

  1. Run Bteq to generate "help view" statements for all the views I want to look at ("select 'Help View ' || databasename || '.' || tablename || ';' from dbc.tables where databasename='<my-database>' and TableKind='V'"), using .export data to put this list in a file.  Also use shell redirection to put stdout and stderr into one file.
  2. Then .EXPORT RESET and .RUN this list of commands.
  3. The result will show all the help commands with view names, and those referencing dropped tables will show error 3807 instead of a list of column names for that view.

But there has to be an easier way to do this!