查询以查找表中没有值的所有列 [英] query to find all columns in a table with no value in it

问题描述

我编写了此查询，用于查找表中没有值但不起作用的列总数.

I have written this query for finding the total number of column which does not have a value in a table but its not working.

SET serveroutput ON;
        DECLARE
          v_count      NUMBER;
          v_table_name VARCHAR2(200);
          CURSOR c2
          IS
            SELECT Column_name FROM all_tab_columns WHERE table_name= A;
        BEGIN
          FOR r1 IN c2
          LOOP
            dbms_output.put_line(r1.column_name);
            EXECUTE immediate('SELECT COUNT(r1.column_name) INTO v_count FROM HR'||'.'||A);
            IF v_count =0 THEN
              dbms_output.put_line(v_count);
            END IF;
          END LOOP;
END;

推荐答案

我希望您尝试以下代码.它对我有用:)

I would like you to try this code. It works for me :)

SET serveroutput ON;
 DECLARE
  myOwner VARCHAR2(20):='HR';
  myTable VARCHAR2(25):='A';
  CURSOR c2
  IS
    SELECT column_name,
      avg_col_len
    FROM all_tab_columns
    WHERE table_name = myTable
    AND owner        = myOwner;
BEGIN
  FOR r1 IN c2
  LOOP
    IF r1.AVG_COL_LEN =0 THEN
      dbms_output.put_line('column_name = '||r1.column_name);
    END IF;
  END LOOP;
END;

希望这可以为您提供帮助.

Hope this can help you.

这篇关于查询以查找表中没有值的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！