Re: add column to many tables at once

From: Michael Austin <>
Date: Sat, 18 Apr 2009 18:40:52 -0500
Message-ID: <uwtGl.29362$>

student4life wrote:
> On Apr 18, 5:35 pm, wrote:
>> On Apr 18, 6:25 pm, student4life <> 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 :
> to use part of it but my plsql IDE is messed up now so I can really
> test it yet.

are you sure you are selecting the correct thing (upper case maybe)? TABLE_NAME and OWNER are generally all uppercase when evaluating "equals".

Do you have access to DBA_TABLES? Received on Sat Apr 18 2009 - 18:40:52 CDT

Original text of this message