OraFAQ Forum: SQL & PL/SQL » ALTER all the tables from a particular schema at one shot

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ALTER all the tables from a particular schema at one shot [message #348458]

Tue, 16 September 2008 16:50

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Report message to a moderator

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

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;
/

Report message to a moderator

Previous Topic:	Value of variable after SELECT INTO returns > 1 row
Next Topic:	does not insert after resolving mutating problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 12:09:56 CDT 2025