Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Views with Missing Tables
 
From:   Glen Blood

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016