CODEWITHSUNDEEP
phpmysql
babi
babi

Reputation: 85

get the size of all columns of a table in mysql

Hi I have used sql query to find the size of column of a table in mysql.This code works for find the size for one column. But i want to get size of all columns of a table in mysql.

$sql="SELECT column_name,  character_maximum_length FROM   information_schema.columns 
     WHERE  table_name = 'user' AND column_name = 'Country'";

if i have tried this code but it did not work:-

<?php

    $sql="SELECT CONCAT(CHARACTER_LENGTH(col1,col2)) FROM tableName 
             WHERE col1 = 'Country' , col2 = 'email'  AND tableName='user'";
    $r= mysql_query($sql);
    $v=mysql_fetch_row($r);
    echo $v[0];
    echo $v[1]; 
?>

Upvotes: 1

Views: 1749

Answers (1)

O. Jones
O. Jones

Reputation: 108651

information_schema.columns is a table. You need to query it with valid sql queries. Here's an example.

 SELECT SUM(character_maximum_length) length,
        table_name
   FROM information_schema.columns 
  WHERE table_name='user'
    AND table_schema=DATABASE()
    AND column_name IN ('Country', 'email')
  GROUP BY table_name

Notice that you have to filter by schema_name (database name) when you fetch information from information_schema tables. Notice also that sizes of columns work differently for numeric and date columns.

Don't rely on this data without experimenting pretty carefully with the results you get from various tables.

And, trying to update these information_schema tables can do bad things to your MySQL server. Don't ask how I know that. :-)

Upvotes: 3

Related Questions