Home » SQL & PL/SQL » SQL & PL/SQL » ALTER all the tables from a particular schema at one shot (Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi)
ALTER all the tables from a particular schema at one shot [message #348458] Tue, 16 September 2008 16:50 Go to next message
thandavakarumuri
Messages: 64
Registered: May 2005
Member
Hi,

Can any one tell me how to ALTER all the tables from a perticular schema at once by simply writing a single SQL Query?

Lets say, I have a Schema Name: Krishna and there are 5 tables in "Krishna" schema.

table1
table2
table3
table4
table5

All the table have a column "Salary Desc" I want to alter the column "Salary Desc" from all the tables in a single shot. I was trying to find out in orafaq.com for this query by entering the serch string "alter all the tables at once", but no luck. I would appreciate if any one could help me in this.

Thanks,
Tandava
Re: ALTER all the tables from a particular schema at one shot [message #348462 is a reply to message #348458] Tue, 16 September 2008 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Write SQL to write SQL & then invoke new SQL
Re: ALTER all the tables from a particular schema at one shot [message #348477 is a reply to message #348458] Tue, 16 September 2008 21:21 Go to previous message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I think ..you can write a PL/SQL block to do that.

begin
for x in (select table_name from cols where column_name='Salary Desc') loop
execute immediate alter table x.table_name ..........;
end loop;
end;
/
Previous Topic: Value of variable after SELECT INTO returns > 1 row
Next Topic: does not insert after resolving mutating problem
Goto Forum:
  


Current Time: Thu Dec 08 16:20:00 CST 2016

Total time taken to generate the page: 0.07459 seconds