CODEWITHSUNDEEP
mysqldatabaseddl
Bob
Bob

Reputation:

Generate table DDL via query on MySQL and SQL Server

Is there an easy way to extract table DDL information, via a query, using either Ms or My SQL server? (preferably both?)

For example, using MySQL Administrator / Navicat for MySql, there is a "DDL" function, which generates the "create table foo (....)" script.

Is there any way to get this information from a query itself, such as:

Select DDL from foo where table_name='bar';

Any have the "Create table bar (.....)" returned to me?

If not - any suggestions?

Upvotes: 50

Views: 62811

Answers (4)

JayRizzo
JayRizzo

Reputation: 3616

I think you are looking for something like this.

These queries generate results that you can copy and paste or use to automate:

Queries based off of "Search text in fields in every table of a MySQL database"

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

Tables


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;

Views


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
;

Stored Procedures/Routines


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
;

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415921

You can't get the CREATE Table text in a cross platform way, but you can get enough information to build it yourself from the INFORMATION_SCHEMA views.

Upvotes: 1

longneck
longneck

Reputation: 12226

it's mysql-specific, but SHOW CREATE TABLE <table-name> gives you the DDL for a table.

Upvotes: 94

Squirrel
Squirrel

Reputation: 24763

You have to create that yourself.

You can query INFORMATION_SCHEMA.COLUMNS for the column name and data type.

Upvotes: 5

Related Questions