如何在不使用 DESCRIBE 命令的情况下描述 Oracle 中的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9855209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I describe a table in Oracle without using the DESCRIBE command?
提问by patricksweeney
I'm having a hard time with a class I am taking. We need to write an Oracle script that will act just like the DESCRIBE command. The book we are using describes how to work with the Data Dictionary very poorly. Not looking for answers, but a point in the correct direction.
我在上一门课时遇到了困难。我们需要编写一个与 DESCRIBE 命令类似的 Oracle 脚本。我们正在使用的这本书描述了如何非常糟糕地使用数据字典。不是在寻找答案，而是在正确的方向上寻找一个点。
回答by Ben
You're looking for USER_TAB_COLUMNS- all the columns, and their descriptions in the schema the query is executed in - or ALL_TAB_COLUMNS- the same except for all tables that user has permission to view.
您正在寻找USER_TAB_COLUMNS- 所有列及其在执行查询的架构中的描述 - 或ALL_TAB_COLUMNS- 除了用户有权查看的所有表之外。
A typical query might be:
一个典型的查询可能是：
select *
from user_tab_columns
where table_name = 'MY_TABLE'
order by column_id
column_idis the "order" of the column in the table.
column_id是表中列的“顺序”。
You should ensure that 'MY_TABLE' is capitalised unless you've been adding tables with casing ( a bad idea ) in which case you need to use something like = "MyTable".
您应该确保 'MY_TABLE' 大写，除非您一直在添加带有大小写的表（一个坏主意），在这种情况下您需要使用类似= "MyTable".
Specifically descis equivalent to the following which I stole from ss64, a good Oracle resource:
具体desc相当于我从ss64 中窃取的以下内容，这是一个很好的 Oracle 资源：
select column_name as "Name"
, nullable as "Null?"
, concat(concat(concat(data_type,'('),data_length),')') as "Type"
from user_tab_columns
where table_name = 'MY_TABLE';
You can find all of this sort of view by select * from dictionary, which is the top level of the data dictionaryor by looking at the documentation.
您可以找到所有此类视图select * from dictionary，这是数据字典的顶级或通过查看文档。
There is also the DBA_TAB_COLUMNS, which is the same as ALL_TAB_COLUMNS, but for every table in the database. This assumes that you have the privileges to view both it and the tables. If you do not have access to this table you need to get your DBA to grant you the SELECT ANY DICTIONARYprivilege.
还有DBA_TAB_COLUMNS，它与 , 相同ALL_TAB_COLUMNS，但适用于数据库中的每个表。这假定您具有查看它和表的权限。如果您无权访问此表，则需要让您的 DBA 授予您SELECT ANY DICTIONARY权限。
回答by Pop
You can also retrieve the entire command that can be used to recreate the table:
您还可以检索可用于重新创建表的整个命令：
select dbms_metadata.get_ddl('TABLE','<my table name>','<table owner>') from dual;
回答by Kaushik Nayak
Newly introduced in Oracle SQLclis the informationcommand or simply INFO table_name.
It has a simple syntax like DESC[RIBE]:
Oracle 中新引入的SQLcl是information命令或简单的INFO table_name. 它有一个简单的语法，如DESC[RIBE]：
SQL> info
INFORMATION
--------
This command is like describe but with more details about the objects requested.
INFO[RMATION] {[schema.]object[@connect_identifier]}
INFO+ will show column statistics
Its output is far superior and descriptive compared to DESCRIBE. It Lists more detailed information about the column definitions for a table, view or synonym, or the specifications for a function or procedure.
与DESCRIBE. 它列出了有关表、视图或同义词的列定义或函数或过程规范的更多详细信息。
For eg: This is the output I get in SQLcl: Release 18.1.1when I run
例如：这是我在SQLcl: Release 18.1.1 中运行时得到的输出
info employees
info employees
SQL> info employees;
TABLE: EMPLOYEES
LAST ANALYZED:2018-05-26 15:07:58.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*EMPLOYEE_ID NUMBER(6,0) No Primary key of employees table.
FIRST_NAME VARCHAR2(20 BYTE) Yes First name of the employee. A not null column.
LAST_NAME VARCHAR2(25 BYTE) No Last name of the employee. A not null column.
EMAIL VARCHAR2(25 BYTE) No Email id of the employee
PHONE_NUMBER VARCHAR2(20 BYTE) Yes Phone number of the employee; includes country
code and area code
HIRE_DATE DATE No Date when the employee started on this job. A not
null column.
JOB_ID VARCHAR2(10 BYTE) No Current job of the employee; foreign key to job_id
column of the jobs table. A not null column.
SALARY NUMBER(8,2) Yes Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Yes Commission percentage of the employee; Only
employees in sales department elgible for
commission percentage
MANAGER_ID NUMBER(6,0) Yes Manager id of the employee; has same domain as
manager_id in departments table. Foreign key to
employee_id column of employees table.(useful for
reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4,0) Yes Department id where employee works; foreign key to
department_id column of the departments table
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
HR.EMP_JOB_IX NONUNIQUE VALID JOB_ID
HR.EMP_NAME_IX NONUNIQUE VALID LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK UNIQUE VALID EMAIL
HR.EMP_EMP_ID_PK UNIQUE VALID EMPLOYEE_ID
HR.EMP_MANAGER_IX NONUNIQUE VALID MANAGER_ID
HR.EMP_DEPARTMENT_IX NONUNIQUE VALID DEPARTMENT_ID
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_MGR_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
EMPLOYEES EMP_MANAGER_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
JOB_HISTORY JHIST_EMP_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
Here is a screen shot with info+:
这是一个屏幕截图info+：
回答by Glenn
Oracle has a set tables containing meta data about the database structure. There is a table of tables. A table of views. A table of columns. You can query these tables by using views such as USER_TABLES (tables in your schema), ALL_TABLES (tables you have permission to view), DBA_TABLES (all tables, if you have the privileges). More generically, many database vendors support the "information schema" which provides a consistent view of the meta data across vendors. Search for "ALL_TABLES" here and look at all the other information available http://docs.oracle.com/cd/B28359_01/server.111/b28320/toc.htm
Oracle 有一组包含有关数据库结构的元数据的表。有一张桌子。视图表。一个列表。您可以使用诸如 USER_TABLES（您架构中的表）、ALL_TABLES（您有权查看的表）、DBA_TABLES（所有表，如果您有权限的话）等视图来查询这些表。更一般地说，许多数据库供应商支持“信息模式”，它提供跨供应商的元数据的一致视图。在此处搜索“ALL_TABLES”并查看所有其他可用信息http://docs.oracle.com/cd/B28359_01/server.111/b28320/toc.htm
