Archives of the TeradataForum

Message Posted: Fri, 01 Apr 2005 @ 14:54:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

What about looking at it from 180 degrees out.

Build a table viewcounts

Databasename, TABLENAME, counter

Then run the output from:

     SELECT
      'INSERT INTO viewcounts SELECT '''
      || TRIM(DATABASENAME)
     || ''','''
     || TRIM(TABLENAME)
     || ''',count(*) FROM '
     || TRIM(DATABASENAME)
      || '.'
     || TRIM(TABLENAME)
      || ';'
     FROM DBC.TABLES
     WHERE TABLEKIND = 'V'
     ORDER BY 1;

Once finished (it could take a while) OMPARE VIEWCOUNTS to DBC.TABLES

Run

     SELECT DATABASENAME, TABLENAME
      FROM DBC.TABLES
     WHERE (DATABASENAME, TABLENAME)
     NOT IN
      (SELECT DATABASENAME
     , TABLENAME
     from VIEWCOUNTS)
     WHERE TABLEKIND = 'V'
      ORDER BY 1,2;

or

     SELECT DATABASENAME, TABLENAME
      FROM DBC.TABLES
     WHERE TABLEKIND = 'V'
     MINUS
     SELECT DATABASENAME
     , TABLENAME
     from VIEWCOUNTS
      ORDER BY 1,2;

This will give you a list of views that do not work for you. You can then figure out why.

Note: This will give you false positives on views that you do not have permission to run, so ensure that you have all necessary permissions first.

Someday (in my copious free time) I am going to figure out a process to give me all usage relationships between Views, Macros, and Tables.

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>