4 Replies Latest reply: Aug 22, 2013 4:58 PM by Solomon Yakobson RSS

    find the default columns names

    907442
      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
          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
            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
              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

                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.