Potential not nulls

Something to send to a development team of the model or just run the clauses from these generators. Here are ways to figure out potential missing not null constraints.

First a test table

create table nnc(n number, m number);

insert into nnc values (1,2);

commit;

By counting the actual rows

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Or another way by using stats

exec dbms_stats.gather_table_stats(user,'NNC');

select t.table_name, c.column_name, t.num_rows 
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;


select '--alter table '||t.table_name||' modify '||c.column_name||' not null;'
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;

And the thirdth option putting those together. Candidates from stats and after that checking the actual data.

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
   and table_name in (
    select t.table_name
      from user_tab_cols c 
     inner join user_tables t 
        on t.table_name=c.table_name
     where c.nullable='Y'
       and t.temporary='N'
       and num_rows > 0
       and num_nulls = 0
   )
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Well maybe the stats version is the one to use.

I prefer having names also to the not null constraints. So after dealing with the developer

alter table NNC modify M constraint nnc_m_nn not null;