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
5 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;