Not Null problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Not Null problems

  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Unhappy

    I have 70 odd tables where the tables have been changed at some point to make all non key fields 'NOT NULL'

    I need to change all of these back to be able to enter nulls. Some of the tables contain data. Is there a way that I can do this in one go rather than modifying each row in 70 odd tables.

    Sati

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Code:
     SELECT table_name,column_name
        FROM user_tab_columns 
      WHERE nullable = 'N';
    You can either spool them and modify them and run that script once or, you can create the script on the fly using the above statements and then edit them and then run

    Code:
      eg: 
            SPOOL alter_table_column.sql
    
            SELECT 'ALTER TABLE '||table_name||'MODIFY ('||column_name||' NULL); '
             FROM user_tab_columns
            WHERE nullable = 'N';
    
            SPOOL OFF;
    Remeber to edit the above script to remove those which has the key constraints enforced.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width