Archives of the TeradataForum

Message Posted: Wed, 05 Mar 2008 @ 09:19:22 GMT

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

Kishore,

Use the Teradata data-dictionary (DBC) to check for object existance, se below for examples.

Jonas Blomqvist

In BTEQ:

     SELECT TOP 1 TableName
     FROM DBC.Tables
     WHERE TableKind = 'T'
     AND TableName = 'VWPRODUCT_LEAF'
     AND DatabaseName= 'the_database'
     ;
     .IF ACTIVITYCOUNT = 0 THEN .GOTO noDROP
     DROP TABLE the_database.VWPRODUCT_LEAF;
     .LABEL noDROP

...or in a Stored Procedure:

     REPLACE PROCEDURE DropObject (
       p_db VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
       ,p_obj VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC
     )
     P0: BEGIN
     DECLARE v_objtype CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC;
     DECLARE v_objname VARCHAR(61) CHARACTER SET LATIN NOT CASESPECIFIC;
     DECLARE v_SQL VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC;

     SELECT MAX(TableKind)
       ,MAX(TRIM(DatabaseName)
       ||TRANSLATE('.' USING UNICODE_TO_LATIN)
       ||TRIM(TableName))
     INTO :v_objtype,v_objname
     FROM DBC.Tables
     WHERE DatabaseName = :p_db
     AND TableName = :p_obj
     ;

     IF v_objtype IS NULL THEN
       LEAVE P0;
     END IF;

     SET v_SQL=TRANSLATE('DROP ' USING UNICODE_TO_LATIN)
       ||TRANSLATE((CASE
         WHEN v_objtype = 'T' THEN 'TABLE'
         WHEN v_objtype = 'V' THEN 'VIEW'
         WHEN v_objtype = 'M' THEN 'MACRO'
         WHEN v_objtype = 'G' THEN 'TRIGGER'
         WHEN v_objtype = 'I' THEN 'JOIN INDEX'
         WHEN v_objtype = 'N' THEN 'HASH INDEX'
         WHEN v_objtype IN ('P','E') THEN 'PROCEDURE'
         WHEN v_objtype IN ('F','A','R') THEN 'SPECIFIC FUNCTION'
         WHEN v_objtype = 'U' THEN 'TYPE'
         WHEN v_objtype = 'X' THEN 'AUTHORIZATION'
         ELSE NULL
         END) USING UNICODE_TO_LATIN)
       ||TRANSLATE(' ' USING UNICODE_TO_LATIN)
       ||v_objname
       ||TRANSLATE(';' USING UNICODE_TO_LATIN)
     ;

     IF v_SQL IS NULL THEN
       LEAVE P0;
     END IF;

     CALL DBC.SysExecSQL(:v_SQL);

     END P0;

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