validate Views


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

Junior Contributor

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.