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.
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
select 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'