CODEWITHSUNDEEP
sqloracle-database
nipoo
nipoo

Reputation: 174

How to display a description of a table in Oracle SQL?

I have to display description of a table in Oracle SQL like below. How can I do that?

I have tried SELECT * FROM ALL_TAB_COMMENTS WHERE table_name = 'abc' but that is not the SQL command for get the the output like below.

Name        Type        Nullable    Default Id  Comments
------- ----------- ------------    ---------   ---------
ABC     VARCHAR2(30)    NOT NULL    1   
PQR     VARCHAR2(2000)  NOT NULL    2   
XYZ     VARCHAR2(30)                3

Upvotes: 1

Views: 7408

Answers (5)

user2340939
user2340939

Reputation: 1981

This shows only the most relevant data and also includes the table and column comments, if they are present:

SELECT
    c.owner,
    c.table_name,
    c.column_name,
    c.data_type,
    c.data_length,
    c.data_precision,
    c.data_scale,
    col_comments.comments AS column_comment,
    tab_comments.comments AS table_comment
FROM
    dba_tab_columns c
LEFT JOIN
    dba_col_comments col_comments
    ON c.owner = col_comments.owner
    AND c.table_name = col_comments.table_name
    AND c.column_name = col_comments.column_name
LEFT JOIN
    dba_tab_comments tab_comments
    ON c.owner = tab_comments.owner
    AND c.table_name = tab_comments.table_name
WHERE
    c.table_name = 'MY_TABLE_NAME';

Upvotes: 0

Laiba Liaquat
Laiba Liaquat

Reputation: 11

Use Describe command:

DESCRIBE { table-Name | view-Name }

Description

Command provides a description of the specified table or view. For a list of tables in the current schema, use the Show Tables command. For a list of views in the current schema, use the Show Views command. For a list of available schemas, use the Show Schemas command.

If the table or view is in a particular schema, qualify it with the schema name.

If the table or view name is case-sensitive, enclose it in single quotes.

You can display all the columns from all the tables and views in a single schema in a single display by using the wildcard character *.

Upvotes: 1

thatjeffsmith
thatjeffsmith

Reputation: 22427

Not a SQL command, but an updated version of DESC[ribe], called INFO[rmation]

Provided by both SQL Developer and SQLcl -

SQL> info locations
TABLE: LOCATIONS 
     LAST ANALYZED:2021-10-10 10:42:07.0 
     ROWS         :23 
     SAMPLE SIZE  :23 
     INMEMORY     :DISABLED 
     COMMENTS     :Locations table that contains specific address of a specific office,
                       warehouse, and/or production site of a company. Does not store addresses /
                       locations of customers. Contains 23 rows; references with the
                       departments and countries tables.  

Columns 
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*LOCATION_ID     NUMBER(4,0)         No               Primary key of locations table
 STREET_ADDRESS  VARCHAR2(40 BYTE)   Yes              Street address of an office, warehouse, or
                                                      production site of a company.Contains building
                                                      number and street name
 POSTAL_CODE     VARCHAR2(12 BYTE)   Yes              Postal code of the location of an office,
                                                      warehouse, or production siteof a company. 
 CITY            VARCHAR2(30 BYTE)   No               A not null column that shows city where an office,
                                                      warehouse, orproduction site of a company is
                                                      located. 
 STATE_PROVINCE  VARCHAR2(25 BYTE)   Yes              State or Province where an office, warehouse, or
                                                      production site of acompany is located.
 COUNTRY_ID      CHAR(2 BYTE)        Yes              Country where an office, warehouse, or production
                                                      site of a company islocated. Foreign key to
                                                      country_id column of the countries table.

Indexes
HR.LOC_ID_PK                UNIQUE        VALID                       LOCATION_ID       HR.LOC_CITY_IX              NONUNIQUE     VALID                       CITY              HR.LOC_COUNTRY_IX           NONUNIQUE     VALID                       COUNTRY_ID        HR.LOC_STATE_PROVINCE_IX    NONUNIQUE     VALID                       STATE_PROVINCE    

References
DEPARTMENTS    DEPT_LOC_FK        NO ACTION      ENABLED    NOT DEFERRABLE    VALIDATED    USER NAME    
SQL>

If you'd rather have column stats vs comments, you can do INFO+

SQL> info+ locations
TABLE: LOCATIONS 
     LAST ANALYZED:2021-10-10 10:42:07.0 
     ROWS         :23 
     SAMPLE SIZE  :23 
     INMEMORY     :DISABLED 
     COMMENTS     :Locations table that contains specific address of a specific office,
                       warehouse, and/or production site of a company. Does not store addresses /
                       locations of customers. Contains 23 rows; references with the
                       departments and countries tables.  

Columns 
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE               HIGH_VALUE              NUM_DISTINCT   HISTOGRAM  
*LOCATION_ID     NUMBER(4,0)         No                   1000                    3200                    23             NONE       
 STREET_ADDRESS  VARCHAR2(40 BYTE)   Yes                  12-98 Victoria Street   Schwanthalerstr. 7031   23             NONE       
 POSTAL_CODE     VARCHAR2(12 BYTE)   Yes                  00989                   YSW 9T2                 22             NONE       
 CITY            VARCHAR2(30 BYTE)   No                   Beijing                 Whitehorse              23             NONE       
 STATE_PROVINCE  VARCHAR2(25 BYTE)   Yes                  BE                      Yukon                   17             NONE       
 COUNTRY_ID      CHAR(2 BYTE)        Yes                                                                  14             NONE       

Indexes
HR.LOC_ID_PK                UNIQUE        VALID                       LOCATION_ID       HR.LOC_CITY_IX              NONUNIQUE     VALID                       CITY              HR.LOC_COUNTRY_IX           NONUNIQUE     VALID                       COUNTRY_ID        HR.LOC_STATE_PROVINCE_IX    NONUNIQUE     VALID                       STATE_PROVINCE    

References
DEPARTMENTS    DEPT_LOC_FK        NO ACTION      ENABLED    NOT DEFERRABLE    VALIDATED    USER NAME    
SQL>

SQLcl has been part of the Oracle Database install (server and client) for some time, but it also available via free license here

Run the same command in SQLDev - enter image description here

Disclaimer: I'm the product manager at Oracle for our database tools including SQLcl and SQL Developer

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

As you want some column data along with its comment, you'll have to join user_tab_columns and user_col_comments.

This is just an example; improve it, if you want:

SQL>   SELECT a.column_name,
  2              a.data_type
  3           || CASE
  4                 WHEN a.data_type = 'NUMBER'
  5                 THEN
  6                    CASE
  7                       WHEN     a.data_precision IS NULL
  8                            AND a.data_scale IS NULL
  9                       THEN
 10                          NULL
 11                       ELSE
 12                             '('
 13                          || TO_CHAR (a.data_precision)
 14                          || ', '
 15                          || TO_CHAR (a.data_scale)
 16                          || ')'
 17                    END
 18                 WHEN a.data_type LIKE '%CHAR%'
 19                 THEN
 20                    '(' || TO_CHAR (a.data_length) || ')'
 21                 ELSE
 22                    NULL
 23              END data_type,
 24           a.nullable,
 25           a.column_id,
 26           b.comments
 27      FROM user_tab_columns a
 28           LEFT JOIN user_col_comments b
 29              ON     a.table_name = b.table_name
 30                 AND a.column_name = b.column_name
 31     WHERE a.table_name = 'GOPLAN'
 32  ORDER BY a.column_id;

Result:

COLUMN_NAME                    DATA_TYPE            N  COLUMN_ID COMMENTS
------------------------------ -------------------- - ---------- --------------------------------------------------
ID_GO_PLAN                     NUMBER               N          1
ID_OSOBA                       NUMBER               N          2 Person ID
GODINA                         NUMBER(4, 0)         N          3 
BROJ_DANA_GO                   NUMBER               N          4 Total number of days
CB_GOTOVO                      NUMBER(1, 0)         N          5 Is the plan verified?
CB_ODOBREN                     NUMBER(1, 0)         N          6 Manager has approved the plan
DATUM_ODOBRENJA                DATE                 Y          7 When did manager approve it?
NAPOMENA_ODOBRENJE             VARCHAR2(200)        Y          8 Additional note

8 rows selected.

SQL>

Upvotes: 2

Gnqz
Gnqz

Reputation: 3382

The thing you look for is probably sqlplus's describe.

You could also see the results from:

SELECT *
  FROM dba_tab_columns
 WHERE table_name = 'abc';

Upvotes: 5

Related Questions