validate Views

Database
Visitor

validate Views

Hello everyone,

 

when i am doing show view databasenae.viewname

its giving me view defination

but when I'm selecting anything it's giving me error

SELECT Failed. 3802:  Database 'databasename' does not exist. 

what is the reason and how to find all such views who have defination but does not exist ?

is there any way we can create a macro or SP so we dont need to run such query again and again.

 

thanks in advance

1 REPLY
Senior Apprentice

Re: validate Views

As Teradata doesn't keep track of where an object is used (and thus doesn't prevent a DROP) you usually have those orphaned views/macros/SPs.

 

For views you can use a cursor within a SP which Selects the view names from dbc.TablesV, executes SELECT 1 INTO dummy FROM db.viewname WHERE 1=0; or and catches any doesn't exist error in a Continue Handler.