/*
  Autor: Edgar Valdez
  Goal: Determinate which columns are null from all tables in a specific schema
  Parameters: You need to change the value of variable: cEsquema writing the schema name
*/
set serveroutput on;
declare nRegistrosTabla int;
        nRegistrosNull int;
        cSQL    varchar2(1000);
        cEsquema  varchar2(50);
        cColumnName varchar2(100);
        cTableName  varchar2(100);
        TYPE cCursor IS REF CURSOR;
        cColumnas cCursor;
        cTablas   cCursor;
        
BEGIN
  cEsquema := 'schema_name';
  cSQL := ' SELECT table_name
            FROM  all_tables
            WHERE owner = ' || '''' || cEsquema || '''';
  
  OPEN cTablas FOR cSQL;
  LOOP
    FETCH cTablas INTO cTableName;
    EXIT WHEN cTablas%NOTFOUND;
      cSQL := 'SELECT COUNT(*) FROM ' || cEsquema || '.' || cTableName;
      BEGIN
        EXECUTE IMMEDIATE cSQL INTO nRegistrosTabla;
        EXCEPTION 
          WHEN OTHERS THEN
          dbms_output.put_line(SQLERRM);  
          dbms_output.put_line(SQLCODE);  
      END;
      
     cSQL := 'SELECT column_name
              FROM all_col_comments
              WHERE owner = ' || '''' || cEsquema || '''' ||
              ' AND TABLE_NAME = ' || '''' || cTableName || '''';  
      OPEN cColumnas FOR cSQL;   
      LOOP
        FETCH cColumnas INTO cColumnname;
        EXIT WHEN cColumnas%NOTFOUND;
        cSQL := 'SELECT COUNT(*) FROM ' || cTableName || ' WHERE ' ||  cColumnname  || ' IS NULL ';      
         BEGIN
          EXECUTE IMMEDIATE cSQL INTO nRegistrosNull;
          EXCEPTION 
            WHEN OTHERS THEN
              dbms_output.put_line(SQLERRM);  
              dbms_output.put_line(SQLCODE);  
        END;
      
        IF nRegistrosTabla = nRegistrosNULL THEN
          dbms_output.put_line(cTableName || '.' || cColumnname);
        END IF;
      END LOOP;
      CLOSE cColumnas;
  END LOOP;
  CLOSE cTablas;
  
END;