Looking for broken views in a database

Analytics
Fan

Looking for broken views in a database

Hi.

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

      , T1.TableName

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

THANKS.

1 REPLY
Teradata Employee

Re: Looking for broken views in a database

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.