--			PROGRAM NAME: NULL_COLUMNS.SQL
--			AUTHOR: 
--			AUGUST 2008
--
--THE PURPOSE OF THIS SCRIPT IS TO IDENTIFY COLUMNS THAT HAVE NULL VALUES, 
--SO THAT A DECISION CAN BE MADE TO DELETE THEM IF IN FACT THEY ARE NOT 
--NEEDED.
--THE MAIN SOURCE TABLE IS THE USER_TAB_COLUMNS.
--
--LOG IN AS THE SCHEMA OWNER.

SET ECHO OFF
--SET ECHO ON
SET SERVEROUTPUT ON SIZE 1000000

SPOOL c:\temp\NULL_COLUMNS.txt

declare
v_TAB_NAME_HOLD	USER_TAB_COLUMNS.TABLE_NAME%TYPE := 'FIRSTTIME';
v_TAB_NAME	USER_TAB_COLUMNS.TABLE_NAME%TYPE := NULL;
v_COL_NAME	USER_TAB_COLUMNS.COLUMN_NAME%TYPE := NULL;
V_COUNT_RECORDS NUMBER(5) := 0;
V_COUNT_COLUMNS NUMBER(5) := 0;
V_COUNT		NUMBER(5) := 0;
V_TOTAL_TABLES  NUMBER(5) := 0;

CURSOR NULL_COL
IS SELECT TABLE_NAME, COLUMN_NAME 
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME NOT LIKE '%$%'
ORDER BY TABLE_NAME, COLUMN_NAME;

begin

--CREATE TABLE NULL_COLL_TEMP(
--TABLE_NAME VARCHAR2(30) NOT NULL,
--COLUMN_NAME VARCHAR2(30) NOT NULL,
--COLUMN_COUNT NUMBER(5),
--RECORD_COUNT NUMBER(20));
 
FOR C_REC IN NULL_COL
LOOP
  V_TAB_NAME := C_REC.TABLE_NAME;
  V_COL_NAME := C_REC.COLUMN_NAME;

IF V_TAB_NAME <> V_TAB_NAME_HOLD THEN
  V_TAB_NAME_HOLD	  := V_TAB_NAME;
  V_TOTAL_TABLES := V_TOTAL_TABLES + 1;

  SELECT COUNT(*) INTO V_COUNT_RECORDS 
  FROM (SELECT DISTINCT(TABLE_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_nAME = V_TAB_NAME_HOLD);

  SELECT COUNT(*) INTO V_COUNT_COLUMNS 
  FROM USER_TAB_COLUMNS 
  WHERE TABLE_NAME = V_TAB_NAME_HOLD;

  DBMS_OUTPUT.PUT_LINE('TABLE NAME IS: ' ||V_TAB_NAME_HOLD);
  DBMS_OUTPUT.PUT_LINE(V_TAB_NAME_HOLD||' TABLE TOTAL RECORDS = '||V_COUNT_RECORDS);
  DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF COLUMNS IN '||V_TAB_NAME_HOLD||' = '||V_COUNT_COLUMNS);
  DBMS_OUTPUT.PUT_LINE('COLUMN NAME IS: ' ||V_COL_NAME);
ELSE
  DBMS_OUTPUT.PUT_LINE('ELSE COLUMN NAME IS: ' ||V_COL_NAME);
  SELECT COUNT(*) INTO V_COUNT FROM (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = v_TAB_NAME
  AND V_COL_NAME IS NOT NULL); 
  IF V_COUNT > 0 THEN
     NULL;
  ELSE
     DBMS_OUTPUT.PUT_LINE('COLUMN '||V_COL_NAME||' IN ' ||V_TAB_NAME_HOLD||' HAS NULL VALUES');
  END IF;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('****** TOTAL NUMBER OF DISTINCT TABLES PROCESSED = '||v_TOTAL_TABLES);

--COMMIT;

END;
/
SPOOL OFF