您可以使用USER_TAB_COLUMNS
字典视图通过单选来实现。如果您希望它为其创建PL/SQL script
,请在for loop
中使用此查询,并以所需的格式使用DBMS_OUTPUT
将其显示为以下输出:
SQL> DESC EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FIRSTNAME VARCHAR2(100)
CITY VARCHAR2(100)
SQL> column "Name" format a41
SQL> column "Null?" format a8
SQL> column "Type" format a28
SQL> SELECT
2 COLUMN_NAME AS "Name",3 DECODE(NULLABLE,'N','NOT NULL') AS "Null?",4 DATA_TYPE
5 || '('
6 || DATA_LENGTH
7 || ')' AS "Type"
8 FROM
9 USER_TAB_COLUMNS
10 WHERE
11 TABLE_NAME = 'EMPLOYEES';
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(22)
FIRSTNAME VARCHAR2(100)
CITY VARCHAR2(100)
SQL>
-更新-
DESCRIBE
或DESC
命令没有提供oracle document中提到的comment column
,并且根据下面的演示。
表,视图,类型和同义词的描述包含
以下信息:
每列的名称
是否为每个值都允许使用null值(NULL或NOT NULL)
列
列的数据类型,例如CHAR,DATE,LONG,LONGRAW,NUMBER,
RAW,ROWID,VARCHAR2(VARCHAR)或XMLType
列的精度(如果有数字列,则可以缩放)
SQL> comment on column EMPLOYEES.ID is 'unique id';
Comment created.
SQL> DESC EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FIRSTNAME VARCHAR2(100)
CITY VARCHAR2(100)
SQL> DESCRIBE EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FIRSTNAME VARCHAR2(100)
CITY VARCHAR2(100)
SQL>
如果您确实希望将comment
作为结果的一部分,请使用以下查询:
SQL> SELECT
2 UT.COLUMN_NAME AS "Name",3 DECODE(UT.NULLABLE,'NOT NULL') AS "Null?",4 UT.DATA_TYPE
5 || '('
6 || UT.DATA_LENGTH
7 || ')' AS "Type",8 UC.COMMENTS
9 FROM
10 USER_TAB_COLUMNS UT
11 JOIN USER_COL_COMMENTS UC ON ( UT.TABLE_NAME = UC.TABLE_NAME
12 AND UT.COLUMN_NAME = UC.COLUMN_NAME )
13 WHERE
14 UT.TABLE_NAME = 'EMPLOYEES';
Name Null? Type COMMENTS
------------------------- -------- -------------------- --------------------
ID NOT NULL NUMBER(22) unique id
FIRSTNAME VARCHAR2(100)
CITY VARCHAR2(100)
SQL>
干杯!
,
只需扩展@Tejash的答案即可。如果要包括评论列,则可以在上述查询中加入USER_COL_COLUMNS视图-
SELECT
UTC.COLUMN_NAME AS "Name",DECODE(NULLABLE,DATA_TYPE
|| '('
|| DATA_LENGTH
|| ')' AS "Type",DATA_DEFAULT "Default",UCC.COMMENTS "EMPLOYEES"
FROM
USER_TAB_COLUMNS UTC
JOIN USER_COL_COMMENTS UCC ON UTC.COLUMN_NAME = UCC.COLUMN_NAME
AND UTC.TABLE_NAME = UCC.TABLE_NAME
WHERE
UTC.TABLE_NAME = 'T1';
我还添加了默认列。 Here是演示。
,
这是您的查询扩展为包括注释。评论来自诸如ALL_TAB_COMMENTS和ALL_COL_COMMENTS之类的视图。该联接是LEFT OUTER JOIN,因为并非所有列都有注释。
注意:您应该使用ALL_%视图,因为该问题表明输入包含owner
,这强烈表明该输入可能针对另一种模式,而不是您所连接的模式。 (可能应该是DBA_%的视图,但是desc
不适用于我们没有特权的表。)
也:您的查询仅处理三种数据类型。说CLOB或CHAR是错误的。
select tc.column_name AS "Name",(case when tc.nullable = 'N'
then 'NOT NULL'
else null
end) AS "Null?",(case when tc.data_type = 'NUMBER' and data_scale > 0
then tc.data_type || '(' || data_precision || ',' || data_scale || ')'
when tc.data_type = 'NUMBER' and data_scale = 0
then tc.data_type || '(' || data_precision || ')'
when tc.data_type in ('VARCHAR2','CHAR')
then tc.data_type || '(' || data_length || ')'
else tc.data_type
end) AS "Type",cc.comments
from all_tab_columns tc
left outer join all_col_columns cc
on cc.owner = tc.owner
and cc.table_name = tc.table_name
and cc.column_name = tc.column_name
where tc.owner = UPPER('&owner')
and tc.table_name = UPPER('&table_name')
order by tc.column_id;
本文链接:https://www.f2er.com/3095138.html