Query if table exists

Database

Query if table exists

How does one query a Teradata database to see if a table exists .... if not create a table

Thanks,
Fred
1 REPLY
Enthusiast

Re: Query if table exists

SELECT * FROM DBC.TABLES
WHERE TABLENAME ='TABLENAME' ;

will tell you if the table exist or not .

If you want to create a conditional statement then you can write DDL for the table and then execute it if the condition is incorrect.

As step 1 Create a bteq that will export the statements as below and next run the bteq.

.logon Db_Server/user,Password

.set format off;
.set width 10000;

.export report file = "c:/Bteq2.sql";

'
select * from dbc.tables
where tablename ='tablename' ;

.IF ACTIVITYCOUNT >0 THEN .GOTO LABEL_SKIP_CREATION; // This means if the table exists then we shd skip the table creation.

CREATE SET TABLE DATABASENAME.${TABLE_NAME}
,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
COLUMNS DEFINTION
)
PRIMART INDEX(COLUMN_NAME);

.LABEL LABEL_SKIP_CREATION

'
.export reset;

.run file = "c:/Bteq2.sql";

.IF ERRORCODE <> 0 THEN .QUIT 100;

.logoff;