Tightening up your data model

SQL> create or replace
  2  procedure check_uncons_columns_for_null(
  3                   p_schema varchar2,
  4                   p_table_name varchar2 default null) is
  5    cursor c_list is
  6     select t.table_name, t.column_name,
  7            max(t.column_name) over (partition by t.table_name  ) as lastcol
  8     from (
  9       select a.table_name, a.column_name
 10       from dba_tab_columns a
 11       where a.owner = p_schema
 12       and a.table_name = nvl(upper(p_table_name),a.table_name)
 13       ) t,
 14       (
 15       select a.table_name, b.column_name, a.search_condition
 16       from dba_cons_columns b, dba_constraints a
 17       where a.owner = p_schema
 18       and   a.constraint_type = 'C'
 19       and   a.table_name = nvl(upper(p_table_name),a.table_name)
 20       and   a.table_name = b.table_name
 21       and   a.owner = b.owner
 22       and   a.constraint_name = b.constraint_name
 23       ) c
 24    where t.table_name = c.table_name(+)
 25    and   t.column_name = c.column_name(+)
 26    order by 1,2;
 27
 28    str0 varchar2(32767);
 29    str1 varchar2(32767);
 30    str2 varchar2(32767);
 31    str3 varchar2(32767);
 32
 33    search_cond varchar2(32767);
 34
 35    prev varchar2(100) := '*';
 36    cnt number;
 37    trailer varchar2(5) := ','||chr(10);
 38
 39  procedure do_sql(thesql varchar2) is
 40    tcursor integer;
 41    dummy integer;
 42  begin
 43    tcursor := dbms_sql.open_cursor;
 44    dbms_sql.parse(tcursor,thesql,2);
 45    dummy := dbms_sql.execute(tcursor);
 46    dbms_sql.close_cursor(tcursor);
 47  end;
 48
 49  begin
 50  for i in c_list loop
 51
 52    if prev != i.table_name then
 53      str0 := 'declare ';
 54      str1 := 'begin select ';
 55      str2 := ' into ';
 56      str3 := ' '; cnt := 1;
 57    end if;
 58
 59    --
 60    -- approximation only
 61    --
 62    if search_cond is null or search_cond != '"'||i.column_name||'" IS NOT NULL' then
 63      str0 := str0 || 'v'||ltrim(cnt)||' number;';
 64      str1 := str1 || 'sum(decode('||i.column_name||',null,1,0))'||trailer;
 65      str2 := str2 || 'v'||ltrim(cnt)||trailer;
 66      str3 := str3 || 'if v'||ltrim(cnt)||' = 0 then '||
 67        'dbms_output.put_line(''alter table '||p_schema||'.'||i.table_name||
 68        ' modify ('||i.column_name||' not null);''); end if;'||chr(10);
 69    end if;
 70    if i.column_name = i.lastcol then
 71      str1 := rtrim(str1,trailer);
 72      str2 := rtrim(str2,trailer) ||' from '||p_schema||'.'||i.table_name||';';
 73      str3 := rtrim(str3,trailer) ||' end;';
 74      do_sql(str0||' '||str1||' '||str2||' '||str3);
 75    end if;
 76    prev := i.table_name;
 77    cnt := cnt + 1;
 78
 79
 80  end loop;
 81  end;
 82  /

Procedure created.

SQL> set serverout on
SQL> exec check_uncons_columns_for_null(user)
alter table SCOTT.CHD modify (A not null);
alter table SCOTT.CHD modify (DATA not null);
alter table SCOTT.CHD modify (F not null);
alter table SCOTT.CHD modify (H not null);
alter table SCOTT.CHD modify (W1 not null);
alter table SCOTT.CHD modify (W2 not null);
alter table SCOTT.PAR modify (A not null);
alter table SCOTT.PAR modify (DATA not null);
alter table SCOTT.PAR modify (F not null);
alter table SCOTT.PAR modify (W not null);

PL/SQL procedure successfully completed.

SQL>
SQL>