Is it Possible to Describe a table and show the NOTNULL columns first?

Question

i cant use ORDER BY with DESC, are there any other ways?

Answer 1

If you just want a list of the columns, you can use the _TAB_COLS views and ORDER BY NULLABLE:

select table_name, column_name, data_type, nullable 
from user_tab_cols
where table_name = 'MYTABLE'
order by nullable, column_name;

Answer 2

You can't do that with the built-in describe command, no. But you can build your own query (as here) but include the nullable flag in the order-by clause:

select column_name as "Column",
  case when nullable = 'N' then 'NOT NULL' end as "Null?",
  cast (data_type || case 
    when data_type in ('VARCHAR2', 'CHAR', 'TIMESTAMP')
      then '(' || data_length || ')'
    when data_type in ('NUMBER')
        and (data_precision is not null or data_scale is not null)
      then '(' || data_precision || case
        when data_scale > 0 then ',' || data_scale
      end || ')'
    end as varchar2(30)) as "Type"
  from user_tab_columns
 where table_name = 'YOUR_TABLE'
 order by nullable, column_id;

With a table created with:

create table t42 (id number primary key, col1 varchar2(10),
  col2 number(10,3) not null, col3 date);

That query would give you:

Column                         Null?    Type                         
------------------------------ -------- ------------------------------
ID                             NOT NULL NUMBER                        
COL2                           NOT NULL NUMBER(10,3)                  
COL1                                    VARCHAR2(10)                  
COL3                                    DATE                          

If you're going to use it a lot you could make it a pipelined function that you pass a table name to, perhaps. This won't resolve synonyms or describe stored programs, though.

Answer 3

Just to clarify for you, since you seem unclear on this: DESC is not an SQL command -- it is provided by Oracle as part of SQL*Plus or whichever Oracle utility you are using. That is why there is no ORDER BY. It has it's own syntax which you can find, for example, here.

Answer 4

Yes, you can, just query to all_tab_columns

select *
from ALL_TAB_COLUMNS where nullable = 'N' and table_name = '<TABLE NAME>';