Select columns of specific type in MySQL 4.1

I need to find all smallint (specific开发者_如何转开发ally smallint(5)) columns in a MySQL 4.1 database. It seems that INFORMATION_SCHEMA is only available in MySQL 5+. Is there an alternative?

Thanks

Here's something that should work for you:

select distinct table_name, column_name, column_type
  from information_schema.columns
  where table_schema = 'THE_DB_YOU_WANT_TO_QUERY' 
    and column_type = 'smallint(5)';

Obviously, replace 'THE_DB_YOU_WANT_TO_QUERY' with the actual name of your database. It took me awhile to get all of this to work, but it should work for you.

Use the SHOW COLUMNS syntax to return the data you need.

I think it should work
SHOW TABLES; # Get list of all tables
DESC [EACH TABLE]; # and then check "Type" column

If you are using PHP you could use the following functions to gain information about specific fields in your tables.

mysql_field_type
mysql_fetch_field
mysql_field_len
mysql_field_flags

mysql_fetch_field gives you the most info.

To give you an example of how you might use it:

$SQLquery = 'SELECT * FROM '.$tableName;
$arr;
$field_array = array();
$res = mysql_query($SQLquery);

$numOfCols = mysql_num_fields($res);

for($y=0;$y<=($numOfCols-1);$y++)
{
    $fieldName1 =  mysql_field_name($res, $y);
    $field = mysql_fetch_field($res, $y);
    if($field->max_length < 6 && $field->numeric = true)
    {
      $field_array[] = $fieldName1;
    }
}

I realize this is not a MySql answer but I hope it helps if there is no way to get the info you need via MySql in 4.1