Changing data type of 1000+ existing tables

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Changing data type of 1000+ existing tables

Hi,

 

What is the easiest approach to change the data type of a particular column in all tables in a database? Say, for instance, I do have a data type of COLA defined as INTEGER, FLOAT, DECIMAL in my database and the same COLA is being used extensively in many tables ( 1000 + tables across DBs). Now the source is changing the datatype for VARCHAR to handle alphanumeric characters.

 

I know in Teradata we can use ALTER TABLE statement to modify the precision, but not a datatype. Is there any way than rebuilding the table with the new datatype. Rebuilding will be a very tedious approach for these many tables. ( rename the existing table, deploy new DDL, & insert back all records and remove the temp table ) .

 

Thanks,

1 REPLY 1
Enthusiast

Re: Changing data type of 1000+ existing tables

You may try this, instead of rebuilding the whole table

 

select 'alter table '||trim(databasename)||'.'||trim(tablename)||' add  '||trim(columnname)||'_new varchar(20);  '||'update '||trim(databasename)||'.'||trim(tablename)||' set ' ||trim(columnname)||'_new='||trim(columnname)||';    '||'alter table '||trim(databasename)||'.'||trim(tablename)||' drop  '||trim(columnname)||';  '  from dbc.columns where columnname='COLA' and columntype in('I','I1','I2','I8','D','F' )and databasename in ('Systemfe','test1','test2');

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR