Re: add column to many tables at once

From: student4life <student4lifer_at_gmail.com>
Date: Sat, 18 Apr 2009 16:32:05 -0700 (PDT)
Message-ID: <8d322715-7cfb-4046-9de3-6e023150574e_at_z14g2000yqa.googlegroups.com>

On Apr 18, 5:35 pm, johnbhur..._at_sbcglobal.net wrote:
> On Apr 18, 6:25 pm, student4life <student4li..._at_gmail.com> wrote:
>
> > Could someone show me how  I can add a column to many tables that have
> > names starting with some similar characters? TIA.
>
> Why would you want to do that exactly?  Give us a convincing business
> case that this is some kind of real world business need and you might
> get some help.
>
> Otherwise it just sounds like homework ... perhaps your homework?
>
> Back to you for a question ... what would your sql or plsql look like
> to find a list of table names that start with some similar characters?

ok where I work they have tables prefixed with owner's name. Don't ask me why because I don't know. I use the following script that is supposed to add column on tables based on owner name

select 'alter table '||table_name|| ' add (column_name varchar2);' from all_tables
where owner = 'some_name';

but I got error 'no rows selected' even though the name matched, so I thought I might try a different approach. I am also looking at this plsql :

http://www.oracle.com/technology/oramag/code/tips2004/011104.html

to use part of it but my plsql IDE is messed up now so I can really test it yet. Received on Sat Apr 18 2009 - 18:32:05 CDT