文本框输入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
模式下所有索引的DDLselect
array_to_string(array_agg(indexdef), ';
')
from
(
select
indexname,
indexdef
from
pg_indexes
where
schemaname = 'public') tt或是
public
模式下所有视图的DDLselect
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
模式下,所有物化视图的DDLselect
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