oracle查看数据库所有列,sql – 如何查看oracle数据库中表的列的所有元数据？

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视图.

这是一个快速的例子：

select decode( t.table_name

, lag(t.table_name, 1) over(order by t.table_name)

, null

, t.table_name ) as table_name --

, 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函数：

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

)