Is it possible to see the structure of mulitple table with a single "desc".
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.