oracle数据库中如何查看表的所有列的元数据?
我想知道允许我们查看为oracle数据库中的表定义的所有列的查询.
I want to know the Query that allows us to view all the columns that are defined for a table in oracle database.
细化:
表名:Some_Table 有 10 列.
Table Name: Some_Table have 10 columns.
我想知道如何检索所有列名、它们的数据类型以及为任何列定义的任何约束.
I want to know how I can retrieve the all column names, their data type, and any constraints that are defined for any column.
推荐答案
我想知道如何检索所有列名、它们的数据类型以及为任何列定义的任何约束.
I want to know how I can retrieve the all column names, their data type, and any constraints that are defined for any column.
为此，您可以查询(取决于授予您的权限)[user|all|dba]_tab_columns、[user|all|dba]_cons_columns、<代码>[user|all|dba]_constraints 视图.
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]_constraints views.
这是一个简单的例子:
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');
结果:
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
还可以使用该包的dbms_metadata 包和get_ddl 函数来检索表的完整规范(如果需要):
Also to retrieve a complete specification(if needed) of a table, you can use dbms_metadata package and get_ddl function of that package:
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
)
相关文章