How to rename a table in Teradata?

Posted on by

Contents

Teradata Rename Table

We can rename the existing Teradata table using RENAME TABLE statement.We should have DROP privileges on the table to be renamed, and the appropriate CREATE privileges on its containing database or user.

Syntax for Rename a table in Teradata

If you want to change the table name, you can use the Rename table in Teradata

RENAME TABLE <DatabaseName>.<Old_Table_Name> TO <DatabaseName>.<New_Table_Name>

Example for Rename table in Teradata

In this example, we are renaming the Customer_txn table to Transaction_bkup table in the Banking_DB database.

RENAME TABLE Banking_DB.Customer_Txn 
TO 
Banking_DB.Transaction_bkup
  • Before renaming the table, we need to do the impact analysis such as how many jobs using this table, is there any other table depends on this table and so on. Otherwise it will create the failure or table doesn’t exist error in the dependent jobs. 
  • If the table is not accessed by any jobs, we can rename table with suffix as backup table.

Rename Teradata table if exists using Bteq

We can use the below BTEQ script to validate whether the table exists or not and then we can rename the table in Teradata.

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

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

.LABEL RenameTable
Rename table <DatabaseName>.<Old_Table_Name> TO <DatabaseName>.<New_Table_Name>

Recommended Articles