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

by

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.

More Related Contents:

  1. Maximum Number of times we can query an oracle database [closed]
  2. How to uninstall / completely remove Oracle 11g (client)?
  3. Oracle trigger after insert or delete
  4. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  5. How to handle Day Light Saving in Oracle database
  6. What is the effect of placing the commit after DML in procedure?
  7. Auto Increment for Oracle
  8. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  9. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  10. Oracle Search all tables all columns for string [duplicate]
  11. NLS_NUMERIC_CHARACTERS setting for decimal
  12. creating parameterized views in oracle11g
  13. Create Pivot view in SQL from a SQL table
  14. How to create a user in Oracle 11g and grant permissions
  15. Table-Valued Functions in ORACLE 11g ? ( parameterized views )
  16. Trigger to enforce M-M relationship
  17. Functions vs procedures in Oracle
  18. How to insert a column in a specific position in oracle without dropping and recreating the table?
  19. Get the number of days between two dates in Oracle, inclusive of the dates
  20. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  21. passing in table name as plsql parameter
  22. How to use parameters in a ‘where value in…’ clause?
  23. Why cannot I create triggers on objects owned by SYS?
  24. No more data to read from socket
  25. return resultset from function
  26. ORA-12704: character set mismatch
  27. send a mail if count (*) >0 in oracle
  28. Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
  29. error: ORA-65096: invalid common user or role name in Oracle database
  30. Display names of all constraints for a table in Oracle SQL

Leave a Comment