加载中…
个人资料
  • 博客等级:
  • 博客积分:
  • 博客访问:
  • 关注人气:
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

oracle查看表结构的3种方法

(2018-01-06 22:08:02)
标签:

oracle

分类: Oracle

oracle查看表结构的3种方法


Three ways to get the structure of the table in oracle


Method 1:

The DESCRIBE SQL*PLUS command


SQL> DESC employees

 Name                          Null?    Type

 ----------------------------- -------- -------------------

 EMPLOYEE_ID                   NOT NULL NUMBER(6)

 FIRST_NAME                             VARCHAR2(20)

 LAST_NAME                     NOT NULL VARCHAR2(25)

 EMAIL                         NOT NULL VARCHAR2(25)

 PHONE_NUMBER                           VARCHAR2(20)

 HIRE_DATE                     NOT NULL DATE

 JOB_ID                        NOT NULL VARCHAR2(10)

 SALARY                                 NUMBER(8,2)

 COMMISSION_PCT                         NUMBER(2,2)

 MANAGER_ID                             NUMBER(6)

 DEPARTMENT_ID                          NUMBER(4)



Method 2:

The DBMS_METADATA.GET_DDL package


SQL> SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') FROM dual;


DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')

--------------------------------------------------------------------------------


  CREATE TABLE "HR"."EMPLOYEES"

      "EMPLOYEE_ID" NUMBER(6,0),

        "FIRST_NAME"


SQL> SET LONG 9999

SQL> SET PAGESIZE 100


SQL> SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') FROM dual;


DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')

--------------------------------------------------------------------------------


  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")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "EXAMPLE"  ENABLE,

         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "EXAMPLE"  ENABLE,

         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")

          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,

         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")

          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,

         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")

          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "EXAMPLE"

  

  

Method 3:

Using multiple tables to query the information of the table


user_tab_cols: 用来获取对应用户表的列信息;

user_col_comments: 用来获取对应用户表列的注释信息;

user_constraints: 用来获取用户表的约束条件;

user_cons_columns: 约束中用户可访问列。


SQL> SELECT t.table_name,t.column_name,t.data_type,t.data_length,t.nullable,t.column_id,c.comments,   

       (SELECT CASE WHEN t.column_name = m.column_name THEN 1 ELSE 0 END FROM DUAL) iskey  

   FROM user_tab_cols t, 

        user_col_comments c, 

        (SELECT m.column_name from user_constraints s, user_cons_columns m   

            WHERE m.table_name = 'EMPLOYEES' 

                  AND m.table_name=s.table_name  

                  AND m.constraint_name = s.constraint_name 

                  AND s.constraint_type='P') m  

   WHERE t.table_name = 'EMPLOYEES'

         AND c.table_name = t.table_name   

         AND c.column_name = t.column_name   

         AND t.hidden_column = 'NO'   

   ORDER BY t.column_id;


TABLE_NAME COLUMN_NAME    DATA_TYPE  DATA_LENGTH N  COLUMN_ID COMMENTS                                                                     ISKEY

---------- -------------- ---------- ----------- - ---------- ------------------------------------------------------------------------  --------

EMPLOYEES  EMPLOYEE_ID    NUMBER              22 N          1 Primary key of employees table.                                                  1

EMPLOYEES  FIRST_NAME     VARCHAR2            20 Y          2 First name of the employee. A not null column.                                   0

EMPLOYEES  LAST_NAME      VARCHAR2            25 N          3 Last name of the employee. A not null column.                                    0

EMPLOYEES  EMAIL          VARCHAR2            25 N          4 Email id of the employee                                                         0

EMPLOYEES  PHONE_NUMBER   VARCHAR2            20 Y          5 Phone number of the employee; includes country code and area code                0

EMPLOYEES  HIRE_DATE      DATE                 7 N          6 Date when the employee started on this job. A not null column                    0

EMPLOYEES  JOB_ID         VARCHAR2            10 N          7 Current job of the employee; foreign key to job_id column of the jobs            0

table. A not null column.

EMPLOYEES  SALARY         NUMBER              22 Y          8 Monthly salary of the employee. Must be greater                                  0

                                                              than zero (enforced byemp_salary_min)                                   

EMPLOYEES  COMMISSION_PCT NUMBER              22 Y          9 Commission percentage of the employee; Only employees in                         0

                                                              sales department elgmmission percentage                                 

EMPLOYEES  MANAGER_ID     NUMBER              22 Y         10 Manager id of the employee; has same domain as manager_id in                     0

                                                              departments table. Foreign ey to employee_id column of empl             

                                                              oyees table.(useful for reflexive joins and CONNECT BY query)           

EMPLOYEES  DEPARTMENT_ID  NUMBER              22 Y         11 Department id where employee works; foreign key to                     0

                                                              department_id column of the departments table                                                                                                                              


!!The End!!

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有