how to find field names with dashes in them

[sneakyimp]

I have a large database with over a hundred tables in it. Most of these tables are well-behaved but a couple come from government data and have dashes in the field names which cause me trouble when i do an SQL dump and try to import that into another database.

I don't have much experience with EXPLAIN queries or the like and was wondering if any sql geniuses out there might recommend some code to tell me which of my tables have field names with a dash (-) in them.

[herve]

Something like this :

SHOW TABLES LIKE '%-%';

[sneakyimp]

ah! i wasn't after tables with dashes, but fields. nevertheless your recommendation looks pretty good. can you do that with fields? this is what i'm working with right now:

<?
$path_to_web_root = "";
include("_top.php");

$sql = "SHOW TABLES";
$result = $db->sql_query($sql)
  or myplan_error("Query failed.");
while ($row = $db->sql_fetchrow($result)) {
  echo $row[0] . "<br>";
  $sql2 = "DESCRIBE `" . $row[0] . "`";
  $result2 = $db->sql_query($sql2)
    or die("Query 2 failed:" . $sql2);
  while ($row2 = $db->sql_fetchrow($result2)) {
    $field_name = $row2['Field'];
    if (is_integer(strpos($field_name, "-"))) {
      echo "<span
style='background-color:#ff0000;color#ffffff;'>&nbsp;&nbsp;&nbsp;"
. $row2['Field'] .
"</span><br>";
    }
  }
}
$db->sql_freeresult();
?>

[herve]

Oops, sorry 🙂

That one should be the good one :

SHOW COLUMNS FROM table_name LIKE '%-%';

I don't think you can scan all the tables of the database at once,
but with a loop on table's names it should be ok.