Archives of the TeradataForum

Message Posted: Mon, 11 Mar 2002 @ 20:11:56 GMT

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

I check the REQUESTTEXT field of DBC.TABLES using the LIKE command.

In this example I am looking for all macros in database CORPVMP1 that insert into table LOCATION.

SELECT DATABASENAME (CHAR(18)), TABLENAME (CHAR(18)), TABLEKIND
(CHAR(3))
   FROM DBC.TABLES

     WHERE

       TABLEKIND = 'M' AND

 databasename = 'corpvmp1'

  and

  (

(REQUESTTEXT like '%location%' )

  and

  ((REQUESTTEXT like '%ins %' )

  or

   (REQUESTTEXT like '%insert%' ))

 )

ORDER BY 1,3,2;

If I also want to find who can execute the macros I join it with DBC.ALLRIGHTS.

SELECT a.DATABASENAME (CHAR(12)),
       a.TABLENAME (CHAR(18)),
       a.TABLEKIND (CHAR(3)),
       b.username (char(12)),
       b.accessright (char(3))
   FROM DBC.TABLES a,
        DBC.allrights b
     WHERE
       TABLEKIND = 'M' AND
 a.databasename = 'corpvmp1'
  and
  ((REQUESTTEXT like '%location%' )
  and
  ((REQUESTTEXT like '%ins %' )
  or
   (REQUESTTEXT like '%insert%' ))
  )
and a.databasename = b.databasename
and a.tablename = b.tablename
ORDER BY 1,3,2,4,5;

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