oracle 如何使用 desc 操作显示列的注释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10912337/
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 to show comments of a column with desc operation
提问by Guito
I want the desc table;operation to show the comments of the columns. I have seen that some people achieved this, however I could not find out how. Maybe it depends on the SQL Developer version, mine is 2.1.0.63. Database is Oracle 11g.
我想要desc表；操作以显示列的注释。我看到有些人实现了这一点，但是我不知道如何实现。也许这取决于 SQL Developer 版本，我的是 2.1.0.63。数据库是 Oracle 11g。
This is what I get when doing desc table;:
这就是我在做desc table时得到的；：
Desc table;
Name Nullable Type
------------------- -------- -----
ID NOT NULL NUMBER(38)
ITEM_ID NUMBER(38)
And I would like to get something like this:
我想得到这样的东西：
Desc table;
Name Nullable Type Comment
------------------- -------- ---------- ---------------------------------
ID NOT NULL NUMBER(38) Table's id
ITEM_ID NUMBER(38) Reference to an item
回答by winkbrace
the desc command is interpreted differently for different tools. What it does is do a select of some standard Oracle views.
desc 命令对不同的工具有不同的解释。它所做的是选择一些标准的 Oracle 视图。
Here is a query on those views that will provide the desired column data, but I encourage you to do a select * to see all that is available.
这是对将提供所需列数据的那些视图的查询，但我鼓励您执行 select * 以查看所有可用数据。
You have 3 types of views, the dba_, all_, and user_* views. I use user_* because that is available for each schema/user, but it lists only the objects owned by that schema/user. The dba_ views are typically for dba's only, and the all_ views might or might not be available for you depending on how much your dba's trust you. ^_^
您有 3 种类型的视图，即 dba_ 、 all_和 user_* 视图。我使用 user_* 是因为它可用于每个模式/用户，但它仅列出该模式/用户拥有的对象。dba_ 视图通常仅供 dba 使用，all_ 视图可能对您可用，也可能不可用，具体取决于您的 dba 对您的信任程度。^_^
select tc.column_name
, tc.nullable
, tc.data_type || case when tc.data_type = 'NUMBER' and tc.data_precision is not null then '(' || tc.data_precision || ',' || tc.data_scale || ')'
when tc.data_type like '%CHAR%' then '(' || tc.data_length || ')'
else null
end type
, cc.comments
from user_col_comments cc
join user_tab_columns tc on cc.column_name = tc.column_name
and cc.table_name = tc.table_name
where cc.table_name = upper(:tablename)
回答by Pavel Gatnar
Here is the definition from Oracle SQL Developer (as shown in table column view):
这是 Oracle SQL Developer 的定义（如表列视图中所示）：
SELECT "COLUMN_NAME", "DATA_TYPE", "NULLABLE", "DATA_DEFAULT", "COLUMN_ID", "COMMENTS" FROM(
select c.column_name, case when data_type = 'CHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
when data_type = 'VARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
when data_type = 'VARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
when data_type = 'NCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
when data_type = 'NUMBER' then
case when c.data_precision is null and c.data_scale is null then 'NUMBER'
when c.data_precision is null and c.data_scale is not null then 'NUMBER(38,'||c.data_scale||')'
else data_type||'('||c.data_precision||','||c.data_SCALE||')' end
when data_type = 'NVARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
when data_type = 'NVARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
else data_type end data_type,
decode(nullable,'Y','Yes','No') nullable,
c.DATA_DEFAULT,column_id, com.comments
from sys.Dba_tab_Columns c,
sys.Dba_col_comments com
where c.owner = :OBJECT_OWNER
and c.table_name = :OBJECT_NAME
and c.table_name = com.table_name
and c.owner = com.owner
and c.column_name = com.column_name
order by column_id
)
回答by thatjeffsmith
In Oracle SQLcl, a new modern CLI for Oracle Database, we have DESC. But we have also built a new command called INFO[RMATION].
在Oracle SQLcl（一种用于 Oracle 数据库的新现代 CLI）中，我们有 DESC。但是我们还构建了一个名为 INFO[RMATION] 的新命令。
It by default shows the column comments.
它默认显示列注释。
I am HR on orcl > info locations
TABLE: LOCATIONS
LAST ANALYZED:2017-03-02 17:00:31.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
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
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
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_LOC_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
I am HR on orcl >
If you run INFO+ it replaces column comments with column stats.
如果您运行 INFO+，它将用列统计信息替换列注释。
I am HR on orcl > info+ locations
TABLE: LOCATIONS
LAST ANALYZED:2017-03-02 17:00:31.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 FREQUENCY
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
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
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_LOC_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
I am HR on orcl >
