How to query for a list of tables in a secondary database

Question

I'm working on some Functional tests on a secondary database. Part of the tear down process is to drop tables in the secondary database. How do a make a query like 'show tables' where I can iterate through them and conditionally drop a table when I want to? I've got a connection to the database, like this:

$conn = Database::getConnection('default', 'civicrm_test');
$database = $conn->getConnectionOptions()['database'];

I was looking at Connection->query() but it doesn't seem to return the results? Is there a different way to get the list of tables so I can return them and act on them individually?

Answer 1

$conn = Database::getConnection('default', 'civicrm_test');
$database = $conn->getConnectionOptions()['database'];
$conn->query("SET FOREIGN_KEY_CHECKS = 0");
$tables = $conn->query('SHOW TABLES')->fetchAll();
foreach ($tables as $table_result) {
  $table = $table_result->{"Tables_in_$database"};
  $conn->query("DROP TABLE $table");
}
$conn->query("SET FOREIGN_KEY_CHECKS = 1");
$conn->destroy();

I only needed to disable/enable foreign key checks, because I needed to. Others may not need to.