文本框输入DDL

通过用户在文本框里输入建表语句，创建一个PawSQL的工作空间.

如何获取建表语句

MySQL/Maria DB

您可以通过连接到您的数据库执行以下的SQL语句获取特定表的建表语句.

获取tpch.customer的建表语句

SHOW CREATE table `tpch.customer`;

获取表tpch.customer的DDL, 拷贝到工作空间的文本框。

CREATE TABLE `customer` (
`C_CUSTKEY` int NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` int NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
 PRIMARY KEY `PK_IDX1614428511` (`C_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  -- tpch.orders definition
CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL/Opengauss

获取数据库tpch中public模式下的所有表的建表语句

select
array_to_string(array_agg(query), ';
')
from
(
select
table_name,
'create table ' || table_name || ' (' || string_agg(concat( c1, c2, c3, c4, c5, c6 ), ', ')|| ')' as query
from
(
select
c.table_name,
'"' || c.column_name || '"' || ' ' || case
when data_type = 'array' then ltrim(udt_name, '_')|| '[]'
else data_type
end as c1,
case
when character_maximum_length > 0 then '(' || character_maximum_length || ')'
end as c2,
case
when numeric_precision > 0
and numeric_scale < 1 then null
end as c3,
case
when numeric_precision > 0
and numeric_scale > 0 then null
end as c4,
case
when is_nullable = 'no' then ' not null'
end as c5,
case
when column_default is not null
and column_default not like 'nextval%' then ' default'
end || ' ' || replace(column_default, '::character varying', '') as c6
from
information_schema.columns c,
information_schema.tables t
where
c.table_catalog = 'tpch'
and t.table_catalog = 'tpch'
and t.table_schema = 'public'
and c.table_name = t.table_name
and c.table_schema = 'public'
and t.table_type in ('base table')
order by
c.table_name,
c.ordinal_position ) as string_columns
group by
table_name) dt  

获取public模式下所有索引的DDL

  select
      array_to_string(array_agg(indexdef), ';
  ')
  from
  (
  select
  	indexname,
  	indexdef
  from
  	pg_indexes
  where
  	schemaname = 'public') tt

或是public模式下所有视图的DDL

select
    array_to_string(array_agg(def), '

')
from
    (
    select
        viewname,
        'create view ' || viewname || ' as ' || definition as def
    from
        pg_catalog.pg_views pm
    where
        pm.schemaname = 'public') dt

获取public模式下，所有物化视图的DDL

select
 array_to_string(array_agg(def), '

')
from
(
select
matviewname,
'create materialized view ' || matviewname || ' as
' || definition as def
from
pg_catalog.pg_matviews pm
where
pm.schemaname = 'public') dt

文本框输入DDL

如何获取建表语句​

MySQL/Maria DB​

PostgreSQL/Opengauss​

如何获取建表语句

MySQL/Maria DB

PostgreSQL/Opengauss