June 13, 2011

finding null values columns in the table using sql


finding null values columns in the table using sql

 declare
L_STMT VARCHAR2(32767) := 'SELECT COUNT(1) FROM #1 WHERE #2 IS NULL';
NULL_CNT NUMBER;
TBL_NAME VARCHAR2(30) := 'XYZ';
begin
FOR I IN (SELECT COLUMN_NAME
FROM USER_TABLES
WHERE TABLE_NAME = TBL_NAME
ORDER BY COLUMN_ID)
LOOP
L_STMT := REPLACE(L_STMT, '#1',TBL_NAME);
L_STMT := REPLACE(L_STMT,'#2',I.COLUMN_NAME);
EXECUTE IMMEDIATE L_STMT INTO NULL_CNT;
DBMS_OUTPUT.PUT_LINE ('COLUMN OF ' || TBL_NAME ||' : ' ||' NULL COUNT FOR COLUMN '|| I.COLUMN_NAME ||' IS : ' || NULL_CNT;
END LOOP;
end;

No comments:

Post a Comment