reverse engineer tables.sql

reverse engineer tables.sql

--Useful queries for database(s) with no documentation
--Note: Phpmyadmin, database() will not work and this needs to be a param
SET @inspect_table = 'change_me';
SET @database = database();

--SELECT ALL
SET @s = CONCAT('select * from ', @inspect_table); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

--FIND TABLES LIKE
SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_NAME` LIKE @inspect_table
AND `table_schema` = @database
;

--GET TABLE DETAILS
SELECT 
    REPLACE(`COLUMN_NAME`, CONCAT(`TABLE_NAME`, '_'), '') as `name`,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_COMMENT
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `table_name` = @inspect_table
AND `table_schema` = @database
;

--GET ALL ENUM FIELDS IN A DATABASE TABLE
SELECT 
    `TABLE_NAME`,
    `COLUMN_NAME`,
    COLUMN_TYPE,
    DATA_TYPE,
    COLUMN_COMMENT
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE DATA_TYPE = 'enum'
AND `table_schema` = @database
;

--left join table_tables on (table_tables COLLATE utf8_unicode_ci) = (`TABLE_NAME` COLLATE utf8_unicode_ci)