How to set all column from null to not null in table in oracle

Question

I have 1 table having 40 columns. Out of 40 columns only 5 columns are NOT NULL and rest of the columns are set as NULL. How can i set all NULL column to NOT NULL in one time or in TOAD. Is there any possibility to do this except manually set as NOT NULL.

Im

Generate a script from the data dictionary? The reverse of this, maybe? – Niggle

How are you going to provide values for the columns which currently are set to NULL? – Stereopticon

Answer 1

You can use the Alter Table command to do so. This way:

ALTER TABLE table_name
  MODIFY (column_1 column_type NOT NULL,
          column_2 column_type NOT NULL,
          ...
          column_n column_type);

This will accomplish the changes in all columns at once. Also if your table already has data with null values on those columns, you will have to define default values as well. Here is a sample:

ALTER TABLE table_name
  MODIFY (column_1 varchar2(100) DEFAULT 'some default' NOT NULL,
          column_2 varchar2(75) DEFAULT 'Some Value' NOT NULL);

Brotherson

Aren't default values only needed in case there are null values in the columns? – Dowser

@Dowser Yes, I stated that in the answer. Let me edit to be more clear. – Brotherson

@JorgeCampos i also know about this. That's why i ask, Is there any other way to set all column as NOT NULL instead of do manually. – Im

Not that I know of. And I only added this answer because you mention in your question: "or in TOAD" which means any other ways would be valid. It was not exactly clear that you already knew that. At least not to me :) – Brotherson

@Im Well, it turns out that it may be possible. I have here Toad for MySql so it may be similar to oracle. Go to the Object Explorer, Tables View, Right click on the table you want to change, Select Alter Table option, Select Columns option in the left panel, Then check the not null checkboxes in the right, then click ok. If it works I will edit my answer. – Brotherson

Answer 2

Alter table will work .

alter table [nameOfYourTable] modify [nameOfColumn] [dataType] not null

Admire

Answer 3

You could loop through USER_TAB_COLUMNS ( ALL_TAB_COLUMNS with owner = 'schema') and ALTER using EXECUTE IMMEDIATE.

You could first update the table containing existing NULLs with appropriate value before running this.

SET SERVEROUTPUT ON

BEGIN
    FOR q IN (
            SELECT 'ALTER TABLE ' || table_name || ' MODIFY ' || column_name || ' NOT  NULL' AS query
            FROM user_tab_columns
            WHERE table_name = 'YOURTABLE'
                AND NULLABLE = 'Y'
            ) 
   LOOP
     DBMS_OUTPUT.PUT_LINE(q.query);
     EXECUTE IMMEDIATE q.query;
   END LOOP;
END;

Glittery