用于确定未处理列的元数据 [英] metadata for determining unsed columns
本文介绍了用于确定未处理列的元数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们正在从all_tabs_cols中获取列名,在那里我们得到一些未使用的列,这些列在表中不存在. 如何确定all_tabs_cols中存在的所有未使用的列. 是否存在为此的数据字典表?
We are fetching column names from all_tabs_cols, where we get some unused columns that are not present in the table. How can I determine all the unused columns that are present in all_tabs_cols. Does there exists a data dictionary table for this ?
procedure xxx_mig_db_column_list_proc is
begin
insert into xxx_mig_db_column_list
(id,
owner_name,
table_name,
column_name,
column_seq,
data_type,
is_get_put,
is_base_primary_key,
is_base_primary_key_with_seq,
is_foreign_key,
is_self_ref_foreign_key,
is_conditional_foreign_key,
is_manual,
category,
referred_table_name,
referred_column_name,
manual_fn_to_call,
seq_increment_fn_to_call,
record_manually_altered,
manual_alteration_details)
(select rownum,
s_t_c.owner,
s_t_c.table_name,
s_t_c.column_name,
s_t_c.column_id,
s_t_c.data_type,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL
FROM (SELECT a_t_c.column_name column_name,
a_t_c.column_id column_id,
a_t_c.data_type data_type,
s_t.table_name table_name,
s_t.owner_name owner
FROM all_tab_cols a_t_c, xxx_mig_db_table_list s_t
WHERE s_t.table_name = a_t_c.table_name
AND s_t.owner_name = a_t_c.owner
AND s_t.migrate_yn = 'Y') s_t_c
);
commit;
exception
when others then
xxx_mig_fn_debug_proc('xxx_mig_db_column_list_proc',
'Error : ' || SQLCODE || '*');
end xxx_mig_db_column_list_proc;
推荐答案
See this question. There is another view ALL_TAB_COLUMNS that shows only "used" columns, so you could use that instead of ALL_TAB_COLS, or subtract one from the other to get just the unused columns.
这篇关于用于确定未处理列的元数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文