SQL 如何查看oracle数据库中一个表的所有列的元数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13448860/
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 view all the Metadata of columns of a table in oracle database?
提问by 0o'-Varun-'o0
I want to know the Query that allows us to view all the columns that are defined for a table in oracle database.
我想知道允许我们查看为oracle 数据库中的表定义的所有列的查询。
Elaboration:
细化：
Table Name: Some_Table have 10 columns.
表名：Some_Table 有 10 列。
I want to know how I can retrieve the all column names, their data type, and any constraints that are defined for any column.
我想知道如何检索所有列名、它们的数据类型以及为任何列定义的任何约束。
回答by Nick Krasnov
I want to know how I can retrieve the all column names, their data type, and any constraints that are defined for any column.
我想知道如何检索所有列名、它们的数据类型以及为任何列定义的任何约束。
To do that you can query(depending on privileges granted to you) [user|all|dba]_tab_columns, [user|all|dba]_cons_columns, [user|all|dba]_constraintsviews.
为此，您可以查询（取决于授予您的权限）[user|all|dba]_tab_columns、[user|all|dba]_cons_columns、[user|all|dba]_constraints视图。
Here is a quick example:
这是一个快速示例：
select decode( t.table_name
, lag(t.table_name, 1) over(order by t.table_name)
, null
, t.table_name ) as table_name -- <- just to eliminate
, t.column_name -- repeated tab_name
, t.data_type
, cc.constraint_name
, uc.constraint_type
from user_tab_columns t
left join user_cons_columns cc
on (cc.table_name = t.table_name and
cc.column_name = t.column_name)
left join user_constraints uc
on (t.table_name = uc.table_name and
uc.constraint_name = cc.constraint_name )
where t.table_name in ('EMPLOYEES', 'DEPARTMENTS');
Result:
结果：
TABLE_NAME COLUMN_NAME DATA_TYPE CONSTRAINT_NAME CONSTRAINT_TYPE
------------- ----------------- -------------- -------------------------------
DEPARTMENTS LOCATION_ID NUMBER DEPT_LOC_FK R
DEPARTMENT_ID NUMBER DEPT_ID_PK P
DEPARTMENT_NAME VARCHAR2 DEPT_NAME_NN C
MANAGER_ID NUMBER DEPT_MGR_FK R
EMPLOYEES SALARY NUMBER EMP_SALARY_MIN C
PHONE_NUMBER VARCHAR2
EMPLOYEE_ID NUMBER EMP_EMP_ID_PK P
DEPARTMENT_ID NUMBER EMP_DEPT_FK R
JOB_ID VARCHAR2 EMP_JOB_FK R
MANAGER_ID NUMBER EMP_MANAGER_FK R
COMMISSION_PCT NUMBER
FIRST_NAME VARCHAR2
JOB_ID VARCHAR2 EMP_JOB_NN C
HIRE_DATE DATE EMP_HIRE_DATE_NN C
EMAIL VARCHAR2 EMP_EMAIL_NN C
LAST_NAME VARCHAR2 EMP_LAST_NAME_NN C
EMAIL VARCHAR2 EMP_EMAIL_UK U
17 rows selected
Also to retrieve a complete specification(if needed) of a table, you can use dbms_metadatapackage and get_ddlfunction of that package:
同样要检索表的完整规范（如果需要），您可以使用该包的dbms_metadata包和get_ddl函数：
select dbms_metadata.get_ddl('TABLE', 'EMPLOYEES') as table_ddl
from dual;
table_ddl
--------------------------------------------------------------------------------
CREATE TABLE "HR"."EMPLOYEES"
("EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
-- ... other attributes
)
