Drop Table "if exists"

UDA

Drop Table "if exists"

Hi,

I can use the Drop Table "if exists" command in my SQL.
but when I use this command in Teradata I get an error message.

Is there any way I can drop a table only if it already exists in Teradata SQL Assistant 7.1?

Thanks
10 REPLIES
Enthusiast

Re: Drop Table "if exists"

Hi,
You cannot do this from Queryman, no matter what version you are using. The only option is to use BTEQ for this operation. You can use something like below:

SELECT 1
FROM DBC.TABLES
WHERE Databasename = ''
AND Tablename (= '' or like '%%');
.if activitycount = 0 then .goto no_tab;
.if errorcode <> 0 then .goto unsuccessful_end;

DROP TABLE $dwh.table_name;
.if errorcode <> 0 then .goto unsuccessful_end;

.label no_tab;
-
-
-
-
-
.label unsuccessful_end;
exit 12;
In this case, if the first query returns any row, it means the table exists and hence activitycount <> 0, the the control continues below and drops the existing table. If the table does not exist, then the first query does not return any data and hence activitycount = 0 wherein the control is transferred to the part with label no_tab which has already skipped the section with the DROP and hence DROP statement will not execute. Hope that helps.
N/A

Re: Drop Table "if exists"

Has anyone been able to create a stored proc or function that accomplishes the same thing and can be run from sql Assistant?

I need to be able to drop a bunch of tables or procedures that may or may not exist so that I can create brand new tables and procedures with the same names without stalling the procedure.
Teradata Employee

Re: Drop Table "if exists"

The solution mentioned above can be easily implemented in Teradata and obviously can be called from SQL Assistant.

Regards, MAC
Enthusiast

Re: Drop Table "if exists"

In BTEQ, the easiest way to do this is to set your ERRORLEVEL severity

.SET ERRORLEVEL 3807 SEVERITY 0

(3807 is the error thrown when an object does not exist)
Now, when you try to drop a table that already exists, it will ignore the error and keep on processing.

R
N/A

Re: Drop Table "if exists"

We used next Teradata procedure in our project.

CREATE PROCEDURE PROD_TEST.DROP_TABLE
(
IN a_tablename VARCHAR(60),
IN a_databasename VARCHAR(60) /*NULL allowed. PROD_TEST used by default*/
)
BEGIN
DECLARE db_name, table_name VARCHAR(60);

SET db_name = TRIM(COALESCE(a_databasename,'PROD_TEST'));
SEt table_name = TRIM(a_tablename);

IF EXISTS(SELECT 1 FROM dbc.tables WHERE databasename = db_name AND tablename = table_name) THEN
CALL DBC.SysExecSQL('DROP TABLE ' || db_name ||'.'|| table_name);
END IF;
END;

Re: Drop Table "if exists"

Hi Adeel.  Can this be done without stored procedure?

Teradata Employee

Re: Drop Table "if exists"

I know this is prevalent on other databases but it really is not necessary. Simply DROP the table. An error will result if the table does not exist but that does not affect anything unless the script or application chooses to do something with the error. If desired, the error code can be checked to make sure it is "table does not exist" rather than some other error.

Re: Drop Table "if exists"

Cheers Todd.  I'm in a bank where the security is pretty strict and we're not allowed use stored procedures etc. so I'm tryinfg to squeeze as much as I can out of sql assistant macros etc.  I did come accross some very interesting syntax with if preceded by a period as follows :

.if activitycount = 0 then .goto no_tab;
.if errorcode <> 0 then .goto unsuccessful_end;

DROP MACRO OETL_STAGE_DIM_ALL_CLEAR;
.if errorcode <> 0 then .goto unsuccessful_end;

.label no_tab;
--EXIT 12;

I would be glad if you you could point me in the direction of a manual/tutorial  which explains this syntax.  Don't worry if not, this response helps a lot.

Best,

Frank

 

Highlighted
Junior Contributor

Re: Drop Table "if exists"

You're not allowed to create SPs, but your DBA can.

 

Talk to your DBA to implement it, after testing as much as they want :-)

 

 

/*
   Drop a table ignoring 3807 error (Table doesn't exist)
   All other errors still return an error
Database & table name will be double quoted to allow non-standard names like 'my table?' If database is not specified it defaults to current DATABASE Drops Volatile Tables when database is set to USER */ 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) || '"."' || COALESCE(tbl_name,'') || '"'; SET sql_stmt = 'DROP TABLE ' || full_name || ';'; EXECUTE IMMEDIATE sql_stmt; SET msg = full_name || ' dropped.'; END;
CALL drop_table_if_exists('mydb', 'mytbl', outmsg); CALL drop_table_if_exists(NULL, 'mytbl', outmsg); -- defaults to DATABASE CALL drop_table_if_exists(USER, 'mytbl', outmsg); -- to drop Volaltile Table