Getting Table And View Definitions For a MySQL Schema (So You Can Recreate It!)

Note: In this instance our schema is called dwh_custom. Change this name as per your requirements.

1) Custom Tables - List Of

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'dwh_custom' AND table_type = 'BASE TABLE' ORDER BY 2 ASC;

2) Custom Tables - Table Create Statements

From the output of the above, for each table you want:

SHOW CREATE TABLE table_schema.table_name;

Note 2A: If you use MySQL via the command line in Windows, the following one liner will convert the output of (1) to your 'SHOW CREATE TABLE' lines. You just need to copy the rows from (1) and save as text file, then run the PowerShell against that file:

Get-Content -path output.txt | Foreach{$_ = $_.replace("|"," ").Trim().replace("     ","."); $_ = ("SHOW CREATE TABLE "+$_+";"); $_}

Note 2B: To process the output from 2A into a more useful output:

$File = "OUTPUT3.txt"; $D = @(); GET-CONTENT $File | FOREACH{ [SYSTEM.ARRAY]$Split = $_.Split("|"); IF( $_.STARTSWITH("mysql") -OR $_.STARTSWITH("+----") -OR $_.STARTSWITH("| Table") -OR $_.STARTSWITH("1 row in set") ){ }ELSEIF( $Split.COUNT -eq 3 ){ $D += $Split[2].TRIM() }ELSEIF( $_.ENDSWITH(" |") ){ $D += ($_ -REPLACE ".{2}$") + ";" }ELSE{ $D += $_ } }; $D

3) Custom Views - List Of

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'dwh_custom' AND table_type = 'VIEW' ORDER BY 2 ASC;

4) Custom Views - View Create Statements

From the output of the above, for each table you want:

SHOW CREATE VIEW table_schema.table_name;

Note 4A: Similarly to in (2), the following one line gives you your SHOW CREATE VIEW lines:

Get-Content -path output2.txt | Foreach{$_ = $_.replace("|"," ").Trim().replace("     ","."); $_ = ("SHOW CREATE VIEW "+$_+";"); $_}

Note 4B: To process the output from 4A into a more useful output:

$File = "OUTPUT4.txt"; $D = @(); GET-CONTENT $File | FOREACH{ [SYSTEM.ARRAY]$Split = $_.Split("|"); IF( $_.STARTSWITH("mysql") -OR $_.STARTSWITH("+----") -OR $_.STARTSWITH("| View") -OR $_.STARTSWITH("1 row in set") ){ }ELSEIF( $Split.COUNT -eq 6 ){ $D += ($Split[2].TRIM() + ";"); $D+= ""}}; $D

BONUS - Procedures and Triggers Too!

1) MySQL Stored Procedures - List Of

SHOW PROCEDURE STATUS WHERE 'Db' = 'dwh_custom';

2) MySQL Stored Procedures - Creates

From the output of the above, for each stored procedure you want:

SHOW CREATE PROCEDURE table_schema.procedure_name

3) MySQL Stored Triggers - List Of

SHOW TRIGGERS FROM dwh_custom;

4) MySQL Stored Triggers - Creates

From the output of the above, for each storage trigger you want:

SHOW CREATE TRIGGER table_schema.trigger_name;