Using Teradata SQL: How to check if a table exists

If you're like me, there are times when you're running SQL that you neeed to drop a table and re-create it as a job runs through. Problem is, if the table doesn't exist when you try to DROP it, your SQL will exit with an error. The way to get around this is to test if the table exists first, and then only issue the DROP command if it's needed.

Checking for a normal table (non-volatile)

This code snippet will do this for you :-)

SELECT 1 FROM dbc.TablesV WHERE databasename = 'MAIN_DB' AND TABLENAME = 'Your_Table_Name';
.IF ACTIVITYCOUNT = 0 THEN GOTO ok
DROP TABLE MAIN_DB.Your_Table_Name;
.LABEL ok

CREATE TABLE Your_Table_Name AS (
...

Note: If it's appropriate, for a non-volatile table, consider clearing the contents of a table rather than doing a DROP..., CREATE..., your DBAs will bless you as it's less load intestive on the database. You can do this using the following command:

DELETE FROM MAIN_DB.Your_Table_Name;

Checking for a volatile table

However, the method outlined above, won't work for volatile tables because they're not recorded in the data dictionary.

Which is a shame because a lot of my jobs use volatile tables, and if you're anything like me, you'll re-run jobs a lot as you're developing them. Of course, you can clear volatile tables by logging out and back in again of course, but that's kinda annoying.

But never fear, the following SP will work for volatile tables :-)

-- Drop the volatile table if it exists
REPLACE PROCEDURE MAIN_DB.drop_voltable(IN pVolTable VARCHAR(32))
BEGIN
  DECLARE sqlstr VARCHAR(500);
  DECLARE exit HANDLER FOR SQLSTATE '42000' BEGIN end;
  SET sqlstr = 'drop table ' || pVolTable;
  EXECUTE IMMEDIATE sqlstr;
end;

CALL MAIN_DB.drop_voltable('Your_Table_Name');

CREATE VOLATILE TABLE Your_Table_Name AS (
...

Note: I would love to credit for both of these solutions, but in truth I found them while googling to solve the problem. Sources and credits are given appropaitely.

Main Source: Thread on "Delete or Drop a table if it already exists" in the Teradata Community
Credit for normal table code: Dieter(dnoeth)/Teradata Community
Credit for volatile table SP: Thorkil/Teradata Community

"It is a capital mistake to theorize before one has data." - Arthur Conan Doyle