빈컬럼 확인
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK OFF
SET VERIFY OFF
DECLARE
v_table_rows NUMBER;
v_not_null_rows NUMBER;
v_sql VARCHAR2(32767);
FUNCTION quote_name(p_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN '"' || REPLACE(p_name, '"', '""') || '"';
END;
BEGIN
FOR t IN (
SELECT table_name
FROM user_tables
WHERE temporary = 'N'
AND nested = 'NO'
AND table_name NOT LIKE 'BIN$%'
ORDER BY table_name
) LOOP
v_sql := 'SELECT COUNT(*) FROM ' || quote_name(t.table_name);
EXECUTE IMMEDIATE v_sql INTO v_table_rows;
FOR c IN (
SELECT column_name
FROM user_tab_columns
WHERE table_name = t.table_name
ORDER BY column_id
) LOOP
IF v_table_rows = 0 THEN
DBMS_OUTPUT.PUT_LINE(t.table_name || '|' || c.column_name);
ELSE
v_sql :=
'SELECT COUNT(*) FROM ' || quote_name(t.table_name) ||
' WHERE ' || quote_name(c.column_name) || ' IS NOT NULL';
EXECUTE IMMEDIATE v_sql INTO v_not_null_rows;
IF v_not_null_rows = 0 THEN
DBMS_OUTPUT.PUT_LINE(t.table_name || '|' || c.column_name);
END IF;
END IF;
END LOOP;
END LOOP;
END;
/
SET FEEDBACK ON
댓글
댓글 쓰기