sql - Is it possible to see the structure of mulitple table with a single "desc".

Question

Is it possible to see the structure of multiple table with a single "desc". i have created following table my_contact, profession, interest,seeking,location etc. but rather then typing "desc" again and again i want to see the structure of selected table with the single query.

Is it possible in anyway?

Answer 1

You can't use the SQL*Plus describe command to get information about more then one object at a time.

On an old question, Ben gave a good overview of how to mimic that client command for a single table by querying the relevant data dictionary view.

To get similar information for more than one table you would need to provide a list of table names, or omit the table name filter altogether. But you probably also want to include the table name in the select list so you know which column belongs to which table, and order the results by table name and column ID, which will mimic the column order as shown by the SQL*Plus describe command.

This expands the data type display from Ben's answer a bit, and should be close to describe for most data types; but with the addition of the table name:

select table_name as "Table",
  column_name as "Column",
  case when nullable = 'N' then 'NOT NULL' end as "Null?",
  cast (data_type || case 
    when data_type in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR')
      then '(' || char_length || case when char_used = 'C' then ' CHAR' else ' BYTE' end || ')'
    when data_type in ('RAW', 'TIMESTAMP')
      then '(' || data_length || ')'
    when data_type in ('NUMBER')
        and (data_precision is not null or data_scale is not null)
      then '(' || coalesce(data_precision, 38) || case
        when data_scale > 0 then ',' || data_scale
      end || ')'
    end as varchar2(30)) as "Type"
  from user_tab_columns
 where table_name in ('MY_CONTACT', 'PROFESSION', 'INTEREST', 'SEEKING', 'LOCATION')
 order by table_name, column_id;

I've mocked up one of your table names using:

create table my_contact (
  id number(38) primary key,
  col1 varchar2(10 char),
  col2 varchar2(32 byte),
  col3 raw(64),
  col4 number(5,2),
  col5 number,
  col6 number(*,3),
  col7 number(*,0),
  col8 clob,
  col9 date,
  col10 timestamp,
  col11 timestamp(3),
  col12 char
);

so with my query I see:

Table                          Column                         Null?    Type                          
------------------------------ ------------------------------ -------- ------------------------------
MY_CONTACT                     ID                             NOT NULL NUMBER(38)                    
MY_CONTACT                     COL1                                    VARCHAR2(10 CHAR)             
MY_CONTACT                     COL2                                    VARCHAR2(32 BYTE)             
MY_CONTACT                     COL3                                    RAW(64)                       
MY_CONTACT                     COL4                                    NUMBER(5,2)                   
MY_CONTACT                     COL5                                    NUMBER                        
MY_CONTACT                     COL6                                    NUMBER(38,3)                  
MY_CONTACT                     COL7                                    NUMBER(38)                    
MY_CONTACT                     COL8                                    CLOB                          
MY_CONTACT                     COL9                                    DATE                          
MY_CONTACT                     COL10                                   TIMESTAMP(6)                  
MY_CONTACT                     COL11                                   TIMESTAMP(3)                  
MY_CONTACT                     COL12                                   CHAR(1)                       

Which is similar to desc:

SQL> desc my_contact
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 COL1                                               VARCHAR2(10 CHAR)
 COL2                                               VARCHAR2(32)
 COL3                                               RAW(64)
 COL4                                               NUMBER(5,2)
 COL5                                               NUMBER
 COL6                                               NUMBER(38,3)
 COL7                                               NUMBER(38)
 COL8                                               CLOB
 COL9                                               DATE
 COL10                                              TIMESTAMP(6)
 COL11                                              TIMESTAMP(3)
 COL12                                              CHAR(1)

If you want to see all your tables then exclude the where clause. And if you want to see other people's tables as well, query all_tab_columns and include the owner in the select list and order by clause; but then you may want to exclude the built in accounts like SYS.

You could also make this a view or a function if you want to run it often but hide the complexity.