I have one database with several view, the problem is that i want make a script for see if some of the 1700 views are broken, i see one article by Rob Paller "http://robpaller.com/archive/2012/09/finding-broken-views-in-teradata/" and the first query give me an error."the query is invalid"
I am using TD 14.10 how server.
DECLARE GetViewNames SCROLL CURSOR FOR
, 'SELECT NULL (TITLE ''ValidView'') FROM ' ||
TRIM(T1.DatabaseName) || '.' || TRIM(T1.TableName) ||
' WHERE 1=2;' AS DMLText
FROM "DBC"."Tables" T1
INNER JOIN "DBC"."Databases" D1
ON T1.DatabaseName = D1.DatabaseName
WHERE T1.TableKind = 'V'
AND D1.OwnerName = :iOwnerDatabaseName
FOR READ ONLY;
my other option was make a script, that it run explain for every view and control the out for create a list of broken views.
Note that the referenced article is talking about coding a Stored Procedure and the code snippets are just some key parts of that procedure. This is not valid as stand-alone SQL, e.g. in a BTEQ script.
A significant advantage to using a SP or a client application (Java, Python, Perl, VB, etc.) is that you can ignore the successes and only log/report the errors. Plus you can just PREPARE the test query and never actually EXECUTE it.
With BTEQ, you could first EXPORT using query similar to the above to generate the test queries, then execute the test queries, then post-process the output from that to remove the successes and report only failures.