check if volatile table exists in teradata

Question

Volatile table are great and somewhat comparable to temp tables in sql server (my background). Is there a way to check if a volatile table exists already? This code won't work when it is run for the first time:

DROP TABLE SomeVolatileTable;
CREATE VOLATILE TABLE SomeVolatileTable AS
(
    SELECT 
        TOP 10 *
    FROM  SomeSourceTable
) WITH DATA ON COMMIT PRESERVE ROWS;

In sql server you can check if a temporary table exists:

IF OBJECT_ID('tempdb..#SomeTempTable') IS NOT NULL DROP TABLE #SomeTempTable

Does something similar exist in Teradata?

Answer 1

There's no way to check if a specific Volatile Table exists besides HELP VOLATILE TABLE which returns all VT.

But you might create a Stored Procedure like the following:

/*
   Drop a table ignoring 3807 error (Table doesn't exist)
*/

REPLACE PROCEDURE drop_table_if_exists
(
  IN db_name VARCHAR(128) CHARACTER SET Unicode,
  IN tbl_name VARCHAR(128) CHARACTER SET Unicode,
  OUT msg VARCHAR(400) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE full_name VARCHAR(361)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(500)  CHARACTER SET Unicode;
   DECLARE exit HANDLER FOR SqlException
   BEGIN
      IF SqlCode = 3807 THEN SET msg = full_name || ' doesn''t exist.';
      ELSE
        RESIGNAL;
      END IF;
   END;

   SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || tbl_name || '"';

   SET sql_stmt = 'DROP TABLE ' || full_name || ';';

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = full_name || ' dropped.';
END;

It will only ignore the Table doesn't exist error, but still fail on invalid rights, etc.

If you call it with your own user as database it also works for Volatile Tables:

CALL drop_table_if_exists(USER,'SomeVolatileTable', msg);