0

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;
Vineet
  • 5,029
  • 10
  • 29
  • 34
  • in what sense do you mean these columns are "unused"? – APC Jul 09 '10 at 15:59
  • possible duplicate of [How can you tell which columns are unused in ALL_TAB_COLS?](http://stackoverflow.com/questions/2949226/how-can-you-tell-which-columns-are-unused-in-all-tab-cols) – sth Jul 13 '10 at 22:44

1 Answers1

1

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.

Community
  • 1
  • 1
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259