如何查找具有非空值的列？

I have many columns in oracle database and some new are added with values. I like to find out which columns have values other than 0 or null. So I am looking for column names for which some sort of useful values exists at least in one row.

我在oracle数据库中有很多列,一些新的值添加了值。我想找出哪些列的值不是0或null。所以我正在寻找至少在一行中存在某些有用值的列名。

How do I do this?

我该怎么做呢?

Update: This sounds very close. How do I modify this to suit my needs?

更新:听起来非常接近。如何修改它以满足我的需求?

select column_name, nullable, num_distinct, num_nulls
from all_tab_columns
where table_name = 'SOME_TABLE'

4 个解决方案

#1

You can query all the columns using the dba_tab_cols view and then see if there are columns which have values other than 0 or null.

您可以使用dba_tab_cols视图查询所有列,然后查看是否存在值为0或null的列。

create or replace function f_has_null_rows(
    i_table_name in dba_tab_cols.table_name%type,
    i_column_name in dba_tab_cols.table_name%type
) return number is
v_sql varchar2(200);
v_count number;
begin
  v_sql := 'select count(*) from ' || i_table_name ||
           ' where ' || i_column_name ' || ' is not null and ' 
           || i_column_name  || ' <>0 ';

  execute immediate v_sql into v_count;

  return v_count;
end;
/


select table_name, column_name from dba_tab_Cols
where  f_has_null_rows (table_name, column_name) > 0

If you have synonyms in some schemas, you mighty find some of the tables are repeated. You'll have to change the code to cater to that.

如果在某些模式中有同义词,则可能会发现某些表重复出现。您必须更改代码才能满足要求。
Also, the check "is not equal to zero" might not be valid for columns that are not integers and will give errors if columns are of date datatype. You'll need to add the conditions for those cases. use the Data_type column in dba_tab_cols and add the condition as needed.

此外,检查“不等于零”可能对非整数的列无效,并且如果列是date数据类型则会给出错误。您需要为这些案例添加条件。使用dba_tab_cols中的Data_type列并根据需要添加条件。

#2

Select Column_name
from user_tab_columns
where table_name='EMP' and num_nulls=0;

This finds columns which does not have any values so you can perform any actions to that.

这会查找没有任何值的列,以便您可以对其执行任何操作。

#3

Sorry, I misread the question the first time.

对不起,我第一次误读了这个问题。

From this post on Oracle's forums

来自Oracle论坛上的这篇文章

Assuming your stats are up to date:

假设您的统计数据是最新的:

SELECT t.table_name,
t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
AND t.num_distinct = 0;

Will return you a list of table names and columns that are null. You might want to add something like:

将返回一个表名和列为空的列表。您可能想要添加以下内容:

AND t.table_name = upper('Your_table_name')

in there to limit the results to just your table.

在那里将结果限制在你的桌子上。

#4

select 'cats' as mycolumname from T 
    where exists (Select id from T where cats is not null)
union
select 'dogs' as mycolumnname from T 
    where exists (select id from T where dogs is not null)

# ad nauseam

is how to do it in SQL. EDIT: Different flavors of SQL might let you optimize with LIMIT or TOP 'n' in the subquery. Or maybe they're even smart enough to realize that EXIST() only needs one row and optimize silently/transparently. P.S. Add your test for zero to the subquery.

是如何在SQL中完成的。编辑:SQL的不同风格可能允许您使用子查询中的LIMIT或TOP'n'进行优化。或者也许他们甚至足够聪明地意识到EXIST()只需要一行并且默默地/透明地优化。附:将测试零添加到子查询中。

#1