0

I am trying to understand the equivalent of this statement 

IF OBJECT_ID('Current') IS NOT NULL 
   DROP TABLE Current;

in Teradata.

Can someone help me out converting this statement to TD14. Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TD123
  • 27
  • 1
  • 9

1 Answers1

1

You can do this in at least newer versions of TD:

select
count (*)
from
dbc.tablesv where tablename = '<your table>'
and databasename = '<your db>'

having count (*) > 0;

.if activitycount = 1  then .GOTO DropTable;
.if activitycount <> 1  then .quit;

.LABEL DropTable
select 'DROP TABLE!';
drop table <your db>.<your table>;

Sadly enough, this won't work with volatile tables. If they are global temporary tables, you can use select count (*) from dbc.AllTempTablesVX where B_tablename =

Andrew
  • 8,445
  • 3
  • 28
  • 46
  • http://stackoverflow.com/questions/9299329/create-table-but-drop-it-if-the-table-exists-already/9300195#9300195 ;) – Rob Paller Dec 04 '14 at 22:19
  • 1
    No, I don't mean in a procedure. That will run in SQL Assistant. – Andrew Dec 04 '14 at 22:32
  • I know. The .IF command in you answer is for a BTEQ script. If you want to run in SQL Assistant you need a stored procedure to implement the logic condition. – Rob Paller Dec 04 '14 at 22:50
  • 1
    No, that exact code (well, with real table and db names) will run as is in SQL Assistant. No BTEQ or stored procs required. – Andrew Dec 04 '14 at 22:54
  • Btw, a COUNT will always return a row :-) – dnoeth Dec 05 '14 at 07:46
  • Learned something about SQLA today. And as @dnoeth mentioned you should change `SELECT COUNT(*)` to `SELECT 1` which will return no rows if the table doesn't exist. Teradata Studio on the other hand doesn't like this approach. :) – Rob Paller Dec 05 '14 at 15:11
  • Made a few changes based on @dnoeth's commments. – Andrew Dec 05 '14 at 15:31