Re: Script to add default value

The following script will create a file called defval.lst, which you can then run (from the schema you wish to change) a) give all your NUMBER columns a default value of 0

b) make all your columns NOT NULL (i.e. change those that weren't already NOT NULL to be NOT NULL)

I hope this is what you wanted.
Be careful!

• * * Top of File * * * set pagesize 0 set feedback off set heading off set termout off spool defval.lst

select 'alter table '¦¦table_name¦¦' modify ( '

        ¦¦column_name¦¦' default 0);'
from user_tab_columns
where data_type = 'NUMBER'
/

select 'alter table '¦¦table_name¦¦' modify ( '

        ¦¦column_name¦¦' NOT NULL);'
from user_tab_columns
where nullable = 'Y'
/

spool off
set termout on
set pagesize
set feedback on
set heading on
* * * End of File * * *

Magnus Bergh wrote in message ...
>I am looking for a script that will change all columns for all tables in
>a schema that allow NULLs to NOT NULL and add a default value (0 for
>numeric fields)?
>
>magnus
Received on Tue Sep 07 1999 - 11:25:25 CDT