17,377
社区成员
发帖
与我相关
我的任务
分享
DECLARE
CURSOR temp IS SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=Upper('TEST');
v_num NUMBER;
BEGIN
FOR i IN temp
LOOP
execute immediate 'SELECT Count(*) FROM test WHERE '||i.COLUMN_NAME||' IS NOT null' INTO v_num;
IF v_num=0 THEN
Dbms_Output.put_line('==columns that have no data=='||i.COLUMN_NAME);
END IF;
NULL;
END LOOP;
END;
CREATE TABLE test(a NUMBER(5),b NUMBER(5),c NUMBER(5));
INSERT INTO test VALUES (100,NULL,null);
INSERT INTO test VALUES (null,200,null);
INSERT INTO test VALUES (300,NULL,null);
COMMIT;
SELECT column_name FROM
(
SELECT 'A'column_name,Max(Length(a))column_len FROM test UNION ALL
SELECT 'B'column_name,Max(Length(b))column_len FROM test UNION ALL
SELECT 'C'column_name,Max(Length(c))column_len FROM test
)WHERE column_len IS NULL;