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 can also describe individual procedures with:
   DESC package.procedure
   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

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

DESC My_Table

is equivalent to

SELECT column_name, nullable, data_type
FROM user_tab_columns
WHERE table_name='MY_TABLE';

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command.

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.

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;

Have you ever been writing code and find yourself typing in a bunch of column names? Why bother when it is all available to you in the Oracle 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 simply 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 Commands/Packages:

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