有可能看到带有单个"desc"的多张桌子的结构. [英] Is it possible to see the structure of mulitple table with a single "desc".
问题描述
是否可以使用单个"desc"查看多个表的结构.我创建了以下表格my_contact，职业，兴趣，寻求，位置等信息，但是我不想一次又一次地输入"desc"，而是想通过单个查询查看所选表格的结构.
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.
反正有可能吗?
推荐答案
You can't use the SQL*Plus describe command to get information about more then one object at a time.
在一个旧问题上，Ben很好地概述了如何通过查询来模拟单个表的客户端命令相关的数据字典视图.
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.
要获取多个表的相似信息，您需要提供表名列表，或者完全省略表名过滤器.但是您可能还希望在选择列表中包括表名，以便知道哪个列属于哪个表，并按表名和列ID对结果进行排序，这将模仿SQL * Plus describe命令显示的列顺序.
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.
这从Ben的回答中扩展了数据类型显示，对于大多数数据类型，应该接近describe；但增加了表名:
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
);
因此我的查询显示:
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)
与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)
如果要查看所有表，请排除where子句.而且，如果您还想查看其他人的表，请查询all_tab_columns并将owner包括在选择列表和order by子句中.但是您可能想排除诸如SYS之类的内置帐户.
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.
这篇关于有可能看到带有单个"desc"的多张桌子的结构.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
