SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';
NUM_ROWS
----------
14
SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
2 where owner='SCOTT' and table_name='EMP' order by column_id;
COLUMN_NAME N NUM_DISTINCT NUM_NULLS
------------------------------ - ------------ ----------
EMPNO N 14 0
ENAME Y 14 0
JOB Y 5 0
MGR Y 6 1
HIREDATE Y 13 0
SAL Y 12 0
COMM Y 4 10
DEPTNO Y 3 0
8 rows selected.
declare
l_table varchar2(30) := 'YOUR_TABLE';
l_statement varchar2(32767);
l_test_statement varchar2(32767);
l_contains_value pls_integer;
-- select column_names from your table
cursor c is
select column_name
,nullable
from user_tab_columns
where table_name = l_table;
begin
l_statement := 'select ';
for r in c
loop
-- If column is not nullable it will always contain a value
if r.nullable = 'N'
then
-- add column to select list.
l_statement := l_statement || r.column_name || ',';
else
-- check if there is a row that has a value for this column
begin
l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' ||
r.column_name || ' is not null)';
dbms_output.put_line(l_test_statement);
execute immediate l_test_statement
into l_contains_value;
-- Yes, add column to select list
l_statement := l_statement || r.column_name || ',';
exception
when no_data_found then
null;
end;
end if;
end loop;
-- create a select statement
l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table;
end;
select rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') col
from (select column_name
from user_tab_columns
where table_name='<table_name>' and low_value is not null)
DECLARE
l_table_name VARCHAR2(255) := 'XXXX';
l_counter NUMBER;
l_sql CLOB;
BEGIN
FOR r_col IN (SELECT *
FROM user_tab_columns tab_col
WHERE table_name = l_table_name
AND data_type NOT IN ('BLOB')
AND column_name NOT IN (SELECT column_name
FROM user_cons_columns con_col
JOIN user_constraints cons ON con_col.constraint_name = cons.constraint_name AND con_col.table_name = cons.table_name
WHERE con_col.table_name = tab_col.table_name
AND constraint_type = 'P')
ORDER BY column_id)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_table_name||' WHERE '||r_col.column_name||' IS NOT NULL'
INTO l_counter;
IF l_counter > 0 THEN
IF l_sql IS NULL THEN
l_sql := r_col.column_name;
ELSE
l_sql := l_sql||','||r_col.column_name;
END IF;
END IF;
END LOOP;
l_sql := 'SELECT '||l_sql||CHR(10)
||'FROM '||l_table_name;
----------
DBMS_OUTPUT.put_line(l_sql);
END;
7条答案
按热度按时间hrirmatl1#
看一下作为统计信息,它可能会对你有用:
例如,您可以检查NUM_NULLS=NUM_ROWS以标识“空”列。
引用:ALL_TAB_COLUMNS、ALL_TABLES。
pw9qyyiw2#
使用以下选项:
Table_Name
必须相应地替换...brccelvz3#
下面是获取非空列的简单代码。
kxkpmulp4#
我不认为这可以在一个查询中完成。您可能需要一些plsql来首先测试哪些列包含数据,并根据该信息编写一条语句。当然,如果表中的数据发生更改,则必须重新创建语句。
wfypjpf45#
i5desfxk6#
此块确定表中的所有列,在动态SQL中循环这些列并检查它们是否为空,然后构造非空查询的DBMS输出查询。
您所要做的就是运行返回的查询。
我已经包括了排除PK和BLOB列。显然,这对于一个接一个地检查列来说是相当慢的,而且对于非常热的表来说也不是很好,因为数据可能更改得太快,但这对我来说很有效,因为我控制了dev env中的流量。
uqdfh47h7#
您要求做的是在整个结果中的每一行上建立一个依赖关系。事实上,这永远不是你想要的。试想一下,如果一行中的每一列的值都为‘0’,那么结果集的模式突然变得包括所有以前“空”的列。现在,您的结果集不仅依赖于表的元数据--而是整个结果集依赖于普通数据。
你要做的就是选择那些你想要的领域,而不是偏离这个简单的计划。