Renaming all tables in a database

New Member

Renaming all tables in a database



I am running a project now which requires me to rename all of the tables in the database. First thing I need to do is make all of the tables upper case. Then I need to remove certain parts of the names like codes to start the name. (eg. "12345_XXXXXXXXX" looking to remove the 12345 code). Is there anyway using teradata that I can rename multiple tables based on these conditions? So rename all tables to have only upper case letters etc.


Thank you



Re: Renaming all tables in a database

1) replace databasename in query below and maybe edit newtablename construction

2) run this query and check result. Result will be sql code that you will use in next step

3) run generated code


tablename as oldtablename,upper(substr(tablename,1,case when instr(tablename,'_')-1>0 then instr(tablename,'_')-1 else 0 end)) as newtablename,'rename table ' || DatabaseName || '.' || TableName || ' to ' || newtablename || ';' as sqlscript

from DBC.Tables
where DatabaseName ='PROD_L1'