How to get the list of table names from database in Drupal

If you want to generate the structure of not what's actually in the database, but how the modules you have activated defined it, you can invoke hook_schema for activated modules. There's actually an API call for it, so all you have to do is to call drupal_get_schema

This is an easy way to get the info, but it won't touch the database, so any table manually created with SQL, or tables that aren't coming from Drupal, or alterations that has been made with raw SQL won't be found. However, in 99.9% of cases it will be accurate.

SQL:

SHOW TABLES;
SHOW COLUMNS FROM table_name;

Try This

global $db_url;
$db_name = explode("/",$db_url);
$dbname = $db_name[count($db_name)-1];
$tables_list = db_query("SHOW tables FROM ".$dbname." WHERE Tables_in_".$dbname." LIKE 'acc%'");
$list_of_tables = array();
while ($result = db_fetch_array($tables_list)) {
drupal_set_message(t('Table name : @db',array('@db'=>$result['Tables_in_'.$dbname.''])));
$list_of_tables[] = $result['Tables_in_'.$dbname.''];
}
//$list_of_tables array contains tables in database.

$columns = db_query("SHOW FIELDS FROM node");
$list_of_columns = array();
while ($res = db_fetch_array($columns)) {
drupal_set_message(t('Column name : @c',array('@c'=>$res['Field'])));
$list_of_columns[] = $res['Field']; 
 }
//$list_of_columns contains columns in the node table.

Try this

  $schema = drupal_get_schema(NULL,TRUE);// Get List Of all tables. 
  ksort($schema);// Sort Ascending
  foreach($schema as $table => $value){
    print_r($table."\r\n");
  } 

Try this piece of code

<?php
    $result = db_query("SHOW TABLES");
    foreach($result as $row){
      print_r($row);  
    }
?>

$row is an object.

$result = db_query("SHOW TABLES");
while($row = db_fetch_array($result)) {
  // Each table should be in $row
  // Same here for "SHOW COLUMNS FROM table_name;" from googletorp answer
}

With Drupal 9^ || 10^ you can do the following:

$names = \Drupal::database()->schema()->findTables('%');