CODEWITHSUNDEEP
phpmysqlnamessqldatatypes
Zetaphor
Zetaphor

Reputation: 504

MySQL retrieve column names where datatype == X

I need to write some code that applies to only certain fields within our database. Specifically I'm looking for any field in all tables of a single database that are of type DECIMAL(12,5);

If it's of any help, all of our decimal fields are prefixed with dec_. I could gather all of the decimal fields from all tables, but there are tons of them that don't match my particular criteria, I need only the decimal values who's limit is 12,5.

I'd have no preference between doing this in pure SQL, or using PHP. We're currently using the CakePHP framework to manage our project, though I could just as easily do this without using the framework. This is a one time operation to gather the required data so I can continue forward with the audit.

Any help is greatly appreciated, thank you for taking the time to read this.

[EDIT] Got some great answers here, thanks everyone! I probably should have mentioned this in my initial post, but is there any way I can also get the table name associated with each field?

Upvotes: 10

Views: 4240

Answers (3)

Wolfack
Wolfack

Reputation: 2769

Try this

SELECT table_name, column_name
FROM information_schema.columns where data_type = 'INT'

Upvotes: 0

SeanCannon
SeanCannon

Reputation: 77966

SELECT
    TABLE_NAME,
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'db-name' AND
    DATA_TYPE = 'decimal' AND
    NUMERIC_PRECISION = 12 AND
    NUMERIC_SCALE = 5

Upvotes: 21

Andrej
Andrej

Reputation: 7504

You can try to use the following approach:

$pdo = new PDO(...);

$sql = "select column_name from 
    information_schema.columns 
    where table_schema='$your_db' and table_name='?' and  data_type = 'decimal(12,5)'";

foreach($pdo->query("SHOW TABLES;") as $table) {
    $prepared = $pdo->prepare($sql);
    $prepared->execute(array($table));
    print_R($prepared->fetchAll()) ;  
}

Upvotes: 2

Related Questions