NOT NULL constraints

Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains

Here's a little routine that lists those columns that don't have a NOT NULL constraint defined, and prints out 'alter table ... ' DDL for those columns that may need such a constraint defined (ie, there are currently no NOT NULL rows). It works across a nominated schema.

create or replace
procedure check_uncons_columns_for_null(
                 p_schema varchar2,
                 p_table_name varchar2 default null) is
  cursor x is
   select table_name, column_name,
          max(column_name) over (partition by table_name  ) as lastcol
   from (
     select a.table_name, a.column_name
     from dba_tab_columns a
     where a.owner = p_schema
     and a.table_name = nvl(upper(p_table_name),a.table_name)
     minus
     select a.table_name, b.column_name
     from dba_cons_columns b, dba_constraints a
     where a.owner = p_schema
     and   a.constraint_type = 'C'
     and   a.table_name = nvl(upper(p_table_name),a.table_name)
     and   a.table_name = b.table_name
     and   a.owner = b.owner
     and   a.constraint_name = b.constraint_name
  ) order by 1,2;

  str0 varchar2(32767);
  str1 varchar2(32767);
  str2 varchar2(32767);
  str3 varchar2(32767);

  prev varchar2(100) := '*';
  cnt number;
  trailer varchar2(5);

procedure do_sql(thesql varchar2) is
  tcursor integer;
  dummy integer;
begin
  tcursor := dbms_sql.open_cursor;
  dbms_sql.parse(tcursor,thesql,2);
  dummy := dbms_sql.execute(tcursor);
  dbms_sql.close_cursor(tcursor);
end;

begin
for i in x loop
  if prev != i.table_name then
    str0 := 'declare ';
    str1 := 'begin select '; str2 := ' into ';
    str3 := ' '; cnt := 1;
  end if;
  if i.column_name = i.lastcol then
    trailer := ' ';
  else
    trailer := ','||chr(10);
  end if;
  str0 := str0 || 'v'||ltrim(cnt)||' number;';
  str1 := str1 || 'sum(decode('||i.column_name||',null,1,0))'||trailer;
  str2 := str2 || 'v'||ltrim(cnt)||trailer;
  str3 := str3 || 'if v'||ltrim(cnt)||' = 0 then '||
    'dbms_output.put_line(''alter table '||p_schema||'.'||i.table_name||
    ' modify ('||i.column_name||' not null);''); end if;'||chr(10);
  if i.column_name = i.lastcol then
    str2 := str2 ||' from '||p_schema||'.'||i.table_name||';';
    str3 := str3 ||' end;';
    do_sql(str0||' '||str1||' '||str2||' '||str3);
  end if;
  prev := i.table_name;
  cnt := cnt + 1;
end loop;
end;