This discussion is archived
4 Replies Latest reply: Aug 22, 2013 2:58 PM by Solomon Yakobson RSS

find the default columns names

907442 Newbie
Currently Being Moderated
Hi Dev's,

Here is my sample table. how to find the list of default column names list.
CREATE TABLE temp_tab
(
a1 VARCHAR2(20),
a2 NUMBER NOT NULL,
a3 VARCHAR2(30) default 'ram',
a4 NUMBER ,
a5 NUMBER default 4
);
my output should be :
a3,a5 columns.


Thanks,
  • 1. Re: find the default columns names
    Purvesh K Guru
    Currently Being Moderated
    select table_name, column_name, data_type, data_default, default_length
      from user_tab_columns   --> You many refer to ALL\DBA_TAB_COLUMNS too depending on your permissions and you must specify the OWNER in such situations as another user may own same table Name
     where data_default is not null
       and table_name = 'TEMP_TAB';
  • 2. Re: find the default columns names
    ranit B Expert
    Currently Being Moderated
    Try this works...
    SELECT *
      FROM user_tab_cols
     WHERE table_name = '<your_table_name>' AND data_default IS NOT NULL;
    You can also use DBA_TAB_COLS or ALL_TAB_COLS, but in that case, also check the 'OWNER' column in WHERE clause.
    It might happen that the same table is present in 2 different Schema and with different column types.
    SELECT *
       FROM dba_tab_cols
     WHERE table_name = '<your_table_name>' AND data_default IS NOT NULL
       and owner = '<your_schema_name>';
    Ranit B.

    Edited by: ranit B on Dec 14, 2012 2:31 PM
  • 3. Re: find the default columns names
    971895 Journeyer
    Currently Being Moderated
    try like...
    select * from 
    user_tab_COLUMNS 
    where table_name='TEST' AND DATA_DEFAULT IS NOT NULL
  • 4. Re: find the default columns names
    Solomon Yakobson Guru
    Currently Being Moderated

    Well, it all depends what we mean by default. Assume we created that table:

     

    SQL> CREATE TABLE temp_tab
      2  (
      3  a1 VARCHAR2(20),
      4  a2 NUMBER NOT NULL,
      5  a3 VARCHAR2(30) default 'ram',
      6  a4 NUMBER ,
      7  a5 NUMBER default 4
      8  );

    Table created.

    SQL>

     

    And now requirements changed and column a3 shouldn't have default value. So we issue:

     

    SQL> alter table temp_tab
      2    modify a3 default null
      3  /

    Table altered.

    SQL>

     

    But column still has DATA_DEFAULT:

     

    SQL> select  data_default
      2    from user_tab_columns
      3    where table_name = 'TEMP_TAB'
      4      and column_name = 'A3'
      5  /

    DATA_DEFAULT
    -------------------------------------
    null

    SQL>

     

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points