我正在寻找从头开始创建给定表所需的“创建表”语句类型。我对主键约束、唯一约束、外键约束和列名特别感兴趣。

我如何在 Oracle 和/或 SQL Server 中执行此操作？

奇怪的是，我与数据库的唯一连接是来自 Linux 机器的 ODBC 连接。尽管对于 Oracle，我几乎肯定可以启动并运行 SQL*Plus。

请您参考如下方法:

在Oracle中使用

select dbms_metadata.get_ddl('TABLE','DEMO_ORDERS') from dual; 

它会返回类似的东西

  CREATE TABLE "OWNER"."DEMO_ORDERS"  
   (    "ORDER_ID" NUMBER NOT NULL ENABLE,  
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE,  
    "ORDER_TOTAL" NUMBER(8,2),  
    "ORDER_TIMESTAMP" DATE,  
    "USER_ID" NUMBER,  
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,  
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID") 
  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) 
  TABLESPACE "USERS"  ENABLE,  
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") 
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE,  
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID") 
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE 
   ) 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) 
  TABLESPACE "USERS"  

您可以使用各种设置来过滤掉不需要的位。

begin 
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true); 
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 
       'SQLTERMINATOR',true); 
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,  
       'SEGMENT_ATTRIBUTES',false); 
end; 

会给予

  CREATE TABLE "OWNER"."DEMO_ORDERS"  
   (    "ORDER_ID" NUMBER NOT NULL ENABLE,  
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE,  
    "ORDER_TOTAL" NUMBER(8,2),  
    "ORDER_TIMESTAMP" DATE,  
    "USER_ID" NUMBER,  
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,  
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID") ENABLE,  
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") 
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE,  
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID") 
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE 
   ) ;