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
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.