jsphdnl jsphdnl - 5 months ago 23
MySQL Question

MySql export only schema without data

I have a list of tables in mysql database, no need to export data, How to export the tables into CSV structure without the data in it, just the structure?

Answer

You can accomplish this by the following query:

OS(Windows):

SELECT 
TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME)
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
GROUP BY TABLE_NAME
INTO OUTFILE 'D:/tableColumnHeaders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

OS(Linux):

SELECT 
TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME)
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
GROUP BY TABLE_NAME
INTO OUTFILE '/tmp/tableColumnHeaders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: Put your desired path after INTO OUTFILE.