metadata for determining unsed columns
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
开发者_Go百科 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.
精彩评论