dbms_matedata.get_ddl_mysql

获取MySQL中某个数据库下所有表建表语句的DDL

本脚本可以获取MySQL中某个数据库下所有表建表语句的DDL
注意:本脚本需在MySQL8.0以上版本运行
注意:本脚本不获取VIEW创建语句
注意:本脚本没有处理主键,外键等约束
注意:本脚本只处理非分区表,分区表请自己改写脚本
注意:如果你做数据迁移,想把MySQL数据库迁移到其他数据库,自己改写脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
WITH tab AS
(SELECT table_name FROM information_schema.TABLES WHERE table_schema = '数据库名' and table_type='BASE TABLE')
SELECT CASE
WHEN id = -1 THEN
concat('create table ', table_name)
WHEN id = 0 THEN
'('
WHEN id = 1 AND max_id = 1 THEN
concat(' ', column_name, ' ', column_type)
WHEN id = 1 AND max_id <> 1 THEN
concat(' ', column_name, ' ', column_type, ',')
WHEN id >= 1 AND id < max_id THEN
concat(' ', column_name, ' ', column_type, ',')
WHEN id = max_id THEN
concat(' ', column_name, ' ', column_type)
WHEN id = 999 THEN
');'
END create_table_ddl
FROM (SELECT table_name,
ordinal_position id,
max(ordinal_position) over(PARTITION BY table_schema, table_name) max_id,
column_name,
concat(column_type,
CASE
WHEN is_nullable = 'NO' THEN
' not null'
ELSE
''
END,
CASE
WHEN COLUMN_DEFAULT IS NULL THEN
''
WHEN COLUMN_DEFAULT IS NOT NULL AND
data_type IN ('varchar', 'char') THEN
concat(' default ', '''', column_default, '''')
WHEN COLUMN_DEFAULT IS NOT NULL AND
data_type NOT IN ('varchar', 'char') THEN
concat(' default ', column_default)
END,
'',
REPLACE(REPLACE(extra, 'DEFAULT_GENERATED', ''),
'auto_increment',
'')) column_type
FROM information_schema.COLUMNS
WHERE table_schema = '数据库名' and table_name in (select table_name from tab)
UNION ALL
SELECT table_name, -1 ordinal_position, NULL, NULL, NULL
FROM tab
UNION ALL
SELECT table_name, 0 ordinal_position, NULL, NULL, NULL
FROM tab
UNION ALL
SELECT table_name, 999 ordinal_position, NULL, NULL, NULL
FROM tab
ORDER BY table_name, id) a;

抓出当前账户下主键,外键,索引,视图,注释,check约束,函数,存储过程等等我就不贴了