Archives of the TeradataForum

Message Posted: Thu, 29 Sep 2011 @ 08:38:13 GMT

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

Hi,

This might get you the views list.

     WITH RECURSIVE
     MYREF_VIEWS(DATABASENAME,TABLENAME,depth,lastaltertimestamp,
                                requesttext ) AS (

     SELECT a.DATABASENAME, a.TABLENAME, 0, A.lastaltertimestamp, A.requesttext

     FROM DBC.TABLES a
     WHERE (a.REQUESTTEXT LIKE '%user_work.samplerank  %'
                     OR          a.REQUESTTEXT LIKE '%user_work.samplerank;' )
                     AND       a.TABLEKIND = 'V'

     UNION
     ALL

     SELECT b.databasename,b.tablename,tmpview.depth+1,b.lastaltertimestamp ,
                                     b.requesttext

     FROM MYREF_VIEWS tmpview, DBC.TABLES b

     WHERE ((b.requesttext LIKE '%' || TRIM(tmpview.DATABASENAME) || '.' ||
     TRIM(tmpview.TABLENAME) || ' %')
                     OR          (b.requesttext LIKE '%' ||
     TRIM(tmpview.DATABASENAME) || '.' || TRIM(tmpview.TABLENAME) || ';' ) )
                     AND       TABLEKIND = 'V'
                     AND       tmpview.depth < 20 )

     SELECT DISTINCT DATABASENAME,TABLENAME
     FROM MYREF_VIEWS
     ORDER BY depth

Lakshman R

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