CODEWITHSUNDEEP
sqloracle-database
Himanshu
Himanshu

Reputation: 3970

How to Alter all columns at once in oracle?

I want to alter all columns data length at once as there are lot of columns min 10-15 and i want to alter all these columns datalength

      alter table states modify id varchar(100);

This works but want similar to (select *...)

       alter table states modify * varchar(100);

this doesnt work as such is there any other way to do this without manually writing all columns names

Thanks.

Upvotes: 0

Views: 839

Answers (1)

pifor
pifor

Reputation: 7882

Here a solution with USER_TAB_COLS (for the current schema) to generate the ALTER TABLE statements:

select 
'alter table ' || table_name || 
' modify ' || column_name || ' varchar2(100);' as code
from user_tab_cols
where table_name='MYTABLE';

NB: This generate SQL statements to change all columns to VARCHAR2: you need to adapt this script to your needs.

Upvotes: 4

Related Questions