0

I have a Huge database and somewhere in some table, there is a field with a datetime format that is corrupted with "0000-00-00 00:00:00" value.

How can I make a query, that will search for given datetime value ex. "0000-00-00 00:00:00" in entire database in all tables and all columns and rows?

Renat Gatin
  • 6,053
  • 5
  • 37
  • 58

3 Answers3

2

With plain mysql I think there is no way. So it depends on the programming language you have available. Ofc. every language is supporting native commands of mysql, which would allow you to write a script performing the required search:

First, you need to fetch all tables: 

SHOW TABLES

https://dev.mysql.com/doc/refman/5.0/en/show-tables.html

Then, in the first loop process every table, retrieving all the columns of the current table: 

 SHOW COLUMNS FROM tableName WHERE TYPE =  'date' -- Maybe you use other types?

https://dev.mysql.com/doc/refman/5.0/en/show-columns.html

Finally, you could use the outer value tableName along with the inner value columnName to retrieve the result, you are seeking for: 

SELECT * from tableName WHERE columnName = '0000-00-00 00:00:00';

So, Script wise it might look like this (pseudocode): 

$tables = query("SHOW TABLES");
foreach($tables as $tableName){
   $columns = query("SHOW COLUMNS FROM $tableName WHERE TYPE =  'date'");
   foreach ($columns AS $columnName) {
      $res = query("SELECT * from $tableName WHERE $columnName = '0000-00-00 00:00:00';");

      foreach ($res AS $row){
         echo "Table $tableName has a invalid date in column $columnName for row {$row["id"]}";
      } 
   }
}
dognose
  • 20,360
  • 9
  • 61
  • 107
0

You could try

SELECT TOP 1(*) FROM yourTable ORDER BY yourDateTimeColumn

The top 1 row should probably be the one with the value you're looking for

BTW, I thought SQL dates aren't earlier than 1900

Piyush Raja
  • 49
  • 1
  • 5
0

You cannot do it in one simple query, but you can make a query that will construct a complicated one for you; I answered a similar question here. In your case, you will probably need to have a DATA_TYPE = 'datetime' condition in the where, instead of the linked example's column_name condition.

Community
  • 1
  • 1
Uueerdo
  • 15,723
  • 1
  • 16
  • 21