How can I describe a table in Oracle without using the DESCRIBE command?

Question

I'm having a hard time with a class I am taking. We need to write an Oracle script that will act just like the DESCRIBE command. The book we are using describes how to work with the Data Dictionary very poorly. Not looking for answers, but a point in the correct direction.

Answer 1

You're looking for USER_TAB_COLUMNS - all the columns, and their descriptions in the schema the query is executed in - or ALL_TAB_COLUMNS - the same except for all tables that user has permission to view.

A typical query might be:

select *
  from user_tab_columns
 where table_name = 'MY_TABLE'
 order by column_id

column_id is the "order" of the column in the table.

You should ensure that 'MY_TABLE' is capitalised unless you've been adding tables with casing ( a bad idea ) in which case you need to use something like = "MyTable".

Specifically desc is equivalent to the following which I stole from ss64, a good Oracle resource:

select column_name as "Name"
     , nullable as "Null?"
     , concat(concat(concat(data_type,'('),data_length),')') as "Type"
  from user_tab_columns
 where table_name = 'MY_TABLE';

You can find all of this sort of view by select * from dictionary, which is the top level of the data dictionary or by looking at the documentation.

There is also the DBA_TAB_COLUMNS, which is the same as ALL_TAB_COLUMNS, but for every table in the database. This assumes that you have the privileges to view both it and the tables. If you do not have access to this table you need to get your DBA to grant you the SELECT ANY DICTIONARY privilege.