在oracle sql中等效于show show table [英] `show create table` equivalent in oracle sql
问题描述
在MySql中,您可以使用show create table table_name
查看表定义(列及其数据类型等).
In MySql you can see the table definition (columns with their data types etc) with show create table table_name
.
oracle sql有类似的功能吗?
Is there a similar functionality for oracle sql?
推荐答案
如果询问SQL * Plus命令(show create table table_name
似乎不是SQL语句),则可以使用desc
命令
If you are asking about SQL*Plus commands (show create table table_name
doesn't appear to be a SQL statement), you can use the desc
command
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
如果您确实需要SQL语句,则可以使用dbms_metadata
程序包
If you really want a SQL statement, you can use the dbms_metadata
package
1 select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' )
2* from dual
SQL> /
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
ALTER INDEX "SCOTT"."PK_EMP" UNUSABLE ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CACHE
根据所使用的工具,可能首先需要运行set long 10000
,这会告诉SQL * Plus显示所选的任何LOB的前10,000个字节.如果您的DDL较长,请设置一个较大的值.
Depending on the tool you are using, you may need to run set long 10000
first, that tells SQL*Plus to display the first 10,000 bytes of any LOB that is selected. If your DDL is longer, set a larger value.
这篇关于在oracle sql中等效于show show table的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!