通过在 MySQL 和 SQL Server 上查询生成表 DDL

Question

有没有一种简单的方法可以使用 Ms 或 My SQL 服务器通过查询来提取表 DDL 信息？ （最好两者都？）

例如，使用 MySQL Administrator / Navicat for MySql，有一个“DDL”函数，它生成“create table foo (....)”脚本。

有什么方法可以从查询本身获取此信息，例如：

Select DDL from foo where table_name='bar';

有“创建表栏（.....）”返回给我吗？

如果没有 - 有什么建议吗？

Answer 1

它是 mysql 特定的，但是 SHOW CREATE TABLE 为您提供表的 DDL。

Answer 2

你必须自己创造它。

您可以查询 INFORMATION_SCHEMA.COLUMNS 以获取列名称和数据类型。

Answer 3

您无法以跨平台方式获取 CREATE Table 文本，但您可以从 INFORMATION_SCHEMA 视图获取足够的信息来自行构建它。

Answer 4

我想你正在寻找这样的东西。

这些查询生成的结果可以复制和粘贴，也可以用于自动化：

基于 "在 MySQL 每个表的字段中搜索文本的查询数据库”

-- KEY
-- CHAR(96) IS BACKTICK
-- CHAR(46) IS PERIOD
-- CHAR(59) IS SEMI-COLON

表

SELECT
CONCAT('SHOW CREATE TABLE '
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `TABLE_SCHEMA`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(46) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `TABLE_NAME`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(59) AS CHAR(8) CHARACTER SET LATIN1)
      ) AS QUERY_GENERATOR
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
      UPPER(`TABLE_SCHEMA`)
        NOT IN (
                'INFORMATION_SCHEMA'
              , 'MYSQL'
              , 'PERFORMANCE_SCHEMA'
              , 'SAKILA'
              , 'SYS'
              , 'WORLD'
               )
  AND UPPER(`TABLE_TYPE`) = 'BASE TABLE'
ORDER BY 1;

视图

SELECT
CONCAT('SHOW CREATE VIEW '
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `TABLE_SCHEMA`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(46) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `TABLE_NAME`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(59) AS CHAR(8) CHARACTER SET LATIN1)
      ) AS QUERY_GENERATOR
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
      UPPER(`TABLE_SCHEMA`)
        NOT IN (
                'INFORMATION_SCHEMA'
              , 'MYSQL'
              , 'PERFORMANCE_SCHEMA'
              , 'SAKILA'
              , 'SYS'
              , 'WORLD'
               )
  AND UPPER(`TABLE_TYPE`) = 'VIEW'
ORDER BY 1
;

存储过程/例程

SELECT
CONCAT('SHOW CREATE PROCEDURE '
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `ROUTINE_SCHEMA`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(46) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1), `ROUTINE_NAME`, CAST(CHAR(96) AS CHAR(8) CHARACTER SET LATIN1)
     , CAST(CHAR(59) AS CHAR(8) CHARACTER SET LATIN1)
      ) AS QUERY_GENERATOR
FROM `INFORMATION_SCHEMA`.`ROUTINES`
WHERE
      UPPER(`ROUTINE_SCHEMA`)
        NOT IN (
                'INFORMATION_SCHEMA'
              , 'MYSQL'
              , 'PERFORMANCE_SCHEMA'
              , 'SAKILA'
              , 'SYS'
              , 'WORLD'
               )
ORDER BY 1
;