Extract data from MySQL for import to diagram tool

Question

I am trying to import SQL to create ERD. I have elected import from SQL and copied the query to my PhpMyAdmin Database admin for the relevant database. The query seems to extract information about all of the databases in my MySQL server, instead of just the database of interest.

This is the code from the import of SQL wizard:

SELECT  'mysql' dbms,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,
        c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,
        n.CONSTRAINT_TYPE,k.REFERENCED_TABLE_SCHEMA,k.REFERENCED_TABLE_NAME,
        k.REFERENCED_COLUMN_NAME
    FROM  INFORMATION_SCHEMA.TABLES t
    LEFT JOIN  INFORMATION_SCHEMA.COLUMNS c  ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
      AND  t.TABLE_NAME=c.TABLE_NAME
    LEFT JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE k  ON c.TABLE_SCHEMA=k.TABLE_SCHEMA
      AND  c.TABLE_NAME=k.TABLE_NAME
      AND  c.COLUMN_NAME=k.COLUMN_NAME
    LEFT JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS n  ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA
      AND  k.CONSTRAINT_NAME=n.CONSTRAINT_NAME
      AND  k.TABLE_SCHEMA=n.TABLE_SCHEMA
      AND  k.TABLE_NAME=n.TABLE_NAME
    WHERE  t.TABLE_TYPE='BASE TABLE'
      AND  t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql');

How do I make it just extract 1 database called jw-app?

score 0 · Answer 1 · answered Nov 22 '18 at 00:23

Your query ends with a WHERE clause which defines the values in t.TABLE_SCHEMA to exclude (i.e. information_schema and mysql).

WHERE t.TABLE_TYPE='BASE TABLE' 
AND t.TABLE_SCHEMA NOT 
IN('INFORMATION_SCHEMA','mysql');

You simply have to reverse that and specify the database name to include instead, either with

t.TABLE_SCHEMA IN('jw_app');

Or, simpler still

t.TABLE_SCHEMA = 'jw_app';

Extract data from MySQL for import to diagram tool

1 Answers1