Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

adding columns to many tables - ORACLE

SOLVED
Go to solution
Stephen Badgett
Regular Advisor

adding columns to many tables - ORACLE

ORACLE - adding columns to many tables..

We have a schema "srb" that has several tables in it that we need to add columns in all the tables in a schema.

example
schema SRB

TABLE A
TABLE B
TABLE C
...
TABLE ZZZ

COLUMNS TO ADD IN EACH

NAME VARCHAR2(100)
ADDRESS VARCHAR2(100)
PHONE NUMBER


I know I can add one at a time but, We have a lot of tables and need to do this without doing it manually.

Any suggestions?
Not as is, is now
8 REPLIES
Alex Lavrov.
Honored Contributor

Re: adding columns to many tables - ORACLE

yep, 2 ways:

1. write select that generates "alter table" statements, something like:
select "alter table " || table_name ...
from dict
wher ....

spool it into some file and then execute it.

2. write pl/sql procedure that executes alter table.

But I prefer the first way.
I don't give a damn for a man that can only spell a word one way. (M. Twain)
Stephen Badgett
Regular Advisor

Re: adding columns to many tables - ORACLE

I am not sure how to do that.
Not as is, is now
Julio Yamawaki
Esteemed Contributor

Re: adding columns to many tables - ORACLE

Hi,

I've been an Oracle DBA for many years and believe, the first way that Alex told you is the fast and best way to do this.
Also, you can make some modifications in the script generated by select..., puting some prompt´s or dbms_output.put_line... to say what your are doing, like, "alter table xxx add column xxx number;" and spooling all output of this script to verify that everything was done.

Regards
Stephen Badgett
Regular Advisor

Re: adding columns to many tables - ORACLE

I do not doubt it in any way I just do not know how to do the syntax of the statement
Not as is, is now
Stephen Badgett
Regular Advisor

Re: adding columns to many tables - ORACLE

Here it is currently -- Are we just to add the add part later?
---------------------------------

select 'alter table ' || table_name from srb_flash
where table_name is not null;
2
'ALTERTABLE'||TABLE_NAME
--------------------------------------------------------------------------------
alter table CUSTOMER
alter table DEPT
alter table EMPLOYEE
alter table NEWTBL
alter table SRB_FLASH


Not as is, is now
Indira Aramandla
Honored Contributor
Solution

Re: adding columns to many tables - ORACLE

Hi Steve,

Yes creating a script with the alter table statements will be an easy solution. Here is how you do it.

SQL> set heading off
SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

This statement will prepare alter table statements for all the tables in the schema SRB and the output will look like this :-

Alter table TABLE_A add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);

Befor you execute the above SQL statement spool to a alter_tab.sql file. Like this

SQL> set heading off

SQL>spool alter_tab.sql

SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

SQL> spool off

SQL>set heading on

Now you will find the sql file alter_tab.sql created in your directory. Now to execute the created alter_tab.sql do this.

SQL>spool alter_tab_output.log
SQL>@alter_tab.sql
SQL>spool off

Now check the alter_tab_output.log file for any errors.

Indira A
Never give up, Keep Trying
Stephen Badgett
Regular Advisor

Re: adding columns to many tables - ORACLE

Perfect -- thank you lots
Not as is, is now
Stephen Badgett
Regular Advisor

Re: adding columns to many tables - ORACLE

You were all help in this and I do find this to be a great solution. Thank you for the answer

Stephen
Not as is, is now