DESC[RIBE] (SQL*Plus command)

Describe an Oracle Table, View, Synonym, package or Function.

Note that because this is a SQL*Plus command you don’t need to terminate it with a semicolon.

Syntax:

   DESC table

   DESC view

   DESC synonym

   DESC function

   DESC package

In Oracle 7 you could describe individual procedures e.g. desc DBMS_UTILITY.GET_PARAMETER_VALUE
In Oracle 8/9/10 you can only describe the whole package: desc DBMS_UTILITY

It is also possible to describe objects in another schema or via a database link
e.g.
DESCRIBE user.table@db_link

Recursive

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command.
For example use the SET commands:
SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
To display these settings use: SHOW DESCRIBE

Data Types

The description for functions and procedures contains the type of PL/SQL object (function or procedure) the name of the function or procedure, the type of value returned (for functions) the argument names, types, whether input or output, and default values, if any.

DESC user.object_name will always identify a distinct database object because a user's database objects must have unique names. e.g. you cannot create a FUNCTION with the same name as a TABLE in the same schema.

Data Dictionary

An alternative to the DESC command is selecting directly from the data dictionary -

DESC MY_TABLE

is equivalent to

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

Column Comments

To view column comments:

SELECT comments
FROM user_col_comments
WHERE table_name='MY_TABLE';

SELECT 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';'
FROM user_col_comments
WHERE comments is not null;

Writing code and find yourself typing in a bunch of column names? Why bother when it’s all available in the data dictionary. The script below will help out:

COL.SQL
-- List all the columns of a table.

select chr(9)||lower(column_name)||',' 
from USER_tab_columns 
where table_Name = UPPER('&1') 
/ 

So now if you want a list of the columns in the EMP table type:
@col emp
This will produce a list of columns:

empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,

“You know the great thing about TV? If something important happens anywhere at all in the world, no matter what time of the day or night, you can always change the channel” ~ Jim Ignatowski

Related Views:

DBA_COL_COMMENTS     ALL_COL_COMMENTS     USER_COL_COMMENTS
DBA_TAB_COMMENTS     ALL_TAB_COMMENTS     USER_TAB_COMMENTS
DICTIONARY
DICT_COLUMNS

Related Oracle Commands/Packages:

COMMENT - Add a comment to a table or a column.
DBMS_DESCRIBE