在sql，oracle中检测，删除空列并更新数据库 [英] Detect, delete empty columns and update database in sql, oracle

问题描述

我有100列，其中一些没有任何值（它们是空的）我如何搜索空列并从表中删除并更新数据库？我试过这个查询，但它不起作用。它显示选择了0行。选择后如何更新数据库？

I have 100 of columns and some of the doesn't have any values inside(they are empty) how can I search for empty columns and delete from table and update database? I tried this query but it doesnt work. It shows 0 rows selected. After selecting how can I update the database?

select table_name, column_name 
from all_tab_columns 
where table_name='some_table'  
and column_name is NULL;

谢谢，

推荐答案

您正在查询数据字典视图。它显示了关于数据库的元数据。此视图ALL_TAB_COLUMNS显示每个表的每一列的信息（您具有权限）。必然COLUMN_NAME不能为空，因此您的查询不返回任何行。

You are querying a data dictionary view. It shows meta-data, in formation about the database. This view, ALL_TAB_COLUMNS, shows information for every column of every table (you have privileges on). Necessarily COLUMN_NAME cannot be null, hence your query returns no rows.

现在您要做的是查询每个表并查找哪些列中没有数据。这需要动态SQL。您将需要查询ALL_TAB_COLUMNS，因此您不是完全偏离基础。

Now what you want to do is query every table and find which columns have no data in them. This requires dynamic SQL. You will need to query ALL_TAB_COLUMNS, so you weren't completely off-base.

由于动态SQL，这是一个编程解决方案，因此结果显示为DBMS_OUTPUT。

Because of dynamic SQL this is a programmatic solution, so the results are displayed with DBMS_OUTPUT.

set serveroutput on size unlimited 

这是一个匿名块：可能需要一些时间才能运行。加入USER_TABLES是必要的，因为视图中的列包含在TAB_COLUMNS中，我们不希望结果集中的列。

Here is an anonymous block: it might take some time to run. The join to USER_TABLES is necessary because columns from views are included in TAB_COLUMNS and we don't want those in the result set.

declare
    dsp varchar2(32767);
    stmt varchar2(32767);
begin
    << tab_loop >>
    for trec in ( select t.table_name
                 from user_tables t )
    loop
        stmt := 'select ';
        dbms_output.put_line('table name = '|| trec.table_name);
        << col_loop >>
        for crec in ( select c.column_name
                             , row_number() over (order by c.column_id) as rn
                      from user_tab_columns c
                      where c.table_name = trec.table_name  
                      and c.nullable = 'Y'
                      order by c.column_id )
        loop
            if rn > 1 then stmt := concat(stmt, '||'); end if;
            stmt := stmt||''''||crec.column_name||'=''||'
                        ||'to_char(count('||crec.column_name||')) ';
        end loop col_loop;
        stmt := stmt || ' from '||trec.table_name;
        execute immediate stmt into dsp;
        dbms_output.put_line(dsp);
    end loop tab_loop;
end;

样本输出：

table name = MY_PROFILER_RUN_EVENTS
TOT_EXECS=0TOT_TIME=0MIN_TIME=0MAX_TIME=0
table name = LOG_TABLE
PKG_NAME=0MODULE_NAME=0CLIENT_ID=0

PL/SQL procedure successfully completed.

SQL> 

COUNT = 0的任何列都没有值。

Any column where the COUNT=0 has no values in it.

现在，您是否真的想放弃这些列是另一回事。您可能会破坏依赖于它们的程序。因此，您首先需要进行影响分析。这就是为什么我没有生成一个自动删除空列的程序。我认为这将是危险的做法。

Now whether you actually want to drop such columns is a different matter. You might break programs which depend on them. So you need an impact analysis first. This is why I have not produced a program which automatically drops the empty columns. I think that would be dangerous practice.

至关重要的是，我们的数据库结构的变更将被考虑和审核。因此，如果我要进行这样的练习，我会改变上面程序的输出，因此它生成了一个drop column语句脚本，我可以查看，编辑并保持在源代码管理下。

It is crucial that changes to our database structure are considered and audited. So if I were ever to undertake an exercise like this I would alter the output from the program above so it produced a script of drop column statements which I could review, edit and keep under source control.

这篇关于在sql，oracle中检测，删除空列并更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！