Archives of the TeradataForum

Message Posted: Fri, 21 Dec 2007 @ 21:04:22 GMT

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

<-- Anonymously Posted: Fri, 21 Dec 2007 14:37 -->

Thanks for responding Michael!

I am using following SQL which gives back all the views created on databasename(view databasename) like '%VWDB%'. View names are like RAPVW55, COMVW86, PNN_COL_VIEW, RAP_EMPL_COD etc. It sorts out alphabetically, all views numeric and nonnumeric separately. My business requirement is to create report which shows Table_database, Tablename and View_database||'.'||viewname, created on each table.

     SEL DATABASENAME,TABLENAME
     FROM  DBC.COLUMNS
     WHERE  COLUMNNAME  LIKE Any( '%CNO%', '%CNU%')
            AND   DATABASENAME
     NOT IN (SEL DATABASENAME
     FROM  DBC.COLUMNS
     WHERE DATABASENAME  LIKE  Any('%TBDB%', '%DBC%','%SYS%','%IDMA%' )
            )
     Group By 1,2
     Order by 1,2

Best regards.

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