How to rename a table in Teradata?
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
|
1 |
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.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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
- Show table/Show view statement in Teradata
- Add new column to a table using alter table statement in Teradata
- Alter table DROP column in Teradata
