Find all tables whose name ends with a certain suffix
Try this:
SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME LIKE '%_History'
OR
SELECT nameFROM sys.tablesWHERE name LIKE '%_History'
Thanks to @Saharsh-shah.
To get table name with all column names in the same query, use this :
SELECT `TABLE_NAME`, group_concat(`COLUMN_NAME` separator ',') AS `COLUMNS`FROM `INFORMATION_SCHEMA`.`COLUMNS`WHERE `TABLE_NAME` IN( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE `TABLE_NAME` LIKE '%_History' )GROUP BY `TABLE_NAME`
You can push it in multidimensional PHP array very simply with the following :
$tables['columns'] = explode(',', $tables['columns']);
Hope it can help some.