Really Null Columns

I was asked to find a column on a table that was null that we could use for another purpose. Since on the requested table, there is close to 200 fields, this didn't seem like a manual task so I wrote some code.

All_Tab_Columns does have the Num_Distinct column, but our site doesn't always analyze each field so some of these say zero, but it's not true. So I start with that list and then check to see if they're truly zero by doing a count on the column.

The first query will generate a spool file with contents that look something like the following: (The two single quotes are needed so the output has one single quote in it.)

SELECT 'insert into nullcols values (''DIST_PHONE_INTL_CODE'');' 
  FROM (SELECT COUNT(DIST_PHONE_INTL_CODE) AS cnt 
          FROM ods_prd.xref_dist ) 
 WHERE cnt = 0 
UNION ALL                                                                                   
SELECT 'insert into nullcols values (''SP_NAME'');' 
  FROM (SELECT COUNT(SP_NAME) AS cnt 
          FROM ods_prd.xref_dist ) 
  WHERE cnt = 0;

This then will spool to the second sql file an insert statement for any column that has a count of zero. When this piece runs, the column names get inserted into a temp table that is joined to All_Tab_Columns to find the type and length.

-- Set In Golden, Press F5 to run all statements after tbl and sch are set

DEFINE sch = ods_prd
DEFINE tbl = xref_dist

-- SQL+ Commands to leave only SQL statements in the spool file
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF

-- Nullcols is needed to hold the columns that needs to be compared to all_tab_columns
-- Drop if it exists and then create.
SPOOL h:\drop.sql
SELECT 'DROP TABLE nullcols;'
  FROM all_tables
 WHERE owner = 'SBX_STR_PRD'
   AND table_name = 'NULLCOLS';
SPOOL OFF
@h:\drop

CREATE TABLE nullcols (nullcol VARCHAR2(30));

--write SQL to this file from the following query
SPOOL h:\test.sql

------------------------------------------------------------------------------------
-- Generate lines as shown to be run next
-- insert into nullcols values ('CLIENT_CHAIN_ID');
------------------------------------------------------------------------------------
WITH max_id AS
        -------------------------------------------------
        -- All lines end with 'UNION ALL' except the last
        -- which needs a ';'
        -------------------------------------------------
        (SELECT MAX(column_id) MAX_ID
           FROM all_tab_columns
          WHERE owner = UPPER('&sch')
            AND table_name = UPPER('&tbl')
            AND num_distinct = 0
         )
SELECT 'SELECT ''insert into nullcols values ('''''
    || column_name
    || ''''');'
    || ''' FROM (SELECT COUNT('
    || column_name
    || ') AS cnt FROM &sch..&tbl ) WHERE cnt = 0'
    || CASE
         WHEN column_id = (SELECT max_id
                             FROM max_id) THEN ';'
         ELSE ' UNION ALL'
       END
  FROM all_tab_columns
 WHERE owner      = UPPER('&sch')
   AND table_name = UPPER('&tbl')
   AND num_distinct = 0
 ORDER BY column_id;

-- close this sql file
SPOOL OFF

-- spool a new sql file with the insert statements
SPOOL h:\test2.sql

-- run the code generated above
@h:\test

-- close this sql code file (insert statements)
spool off

-- run the insert statements
@h:\test2

-- produce a report that shows those columns and their
-- type/length from all_tab_columns
SELECT INITCAP(column_name) AS column_name
     , INITCAP(data_type)
       || CASE
            WHEN data_type <> 'DATE'
            THEN '('
                  || CASE
                       WHEN data_precision IS NULL
                       THEN data_length
                       ELSE TO_NUMBER(data_precision ||'.' || data_scale)
                     END
                  ||')'
          END AS type_length
  FROM all_tab_columns
 WHERE column_name IN (SELECT *
                         FROM nullcols)
   AND owner = UPPER('&sch')
   AND table_name = UPPER('&tbl');