How do I count columns of a table

Question

For example :

tbl_ifo  id | name  | age | gender  ---------------------------- 1  | John  |  15 |  Male 2  | Maria |  18 |  Female 3  | Steph |  19 |  Female 4  | Jay   |  21 |  Male 

How can I count the columns of this table using mysql?

Answer 1

SELECT count(*) FROM information_schema.columns WHERE table_name = 'tbl_ifo' 

Answer 2

I think you need also to specify the name of the database:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'SchemaNameHere'   AND table_name = 'TableNameHere' 

if you don't specify the name of your database, chances are it will count all columns as long as it matches the name of your table. For example, you have two database: DBaseA and DbaseB, In DBaseA, it has two tables: TabA(3 fields), TabB(4 fields). And in DBaseB, it has again two tables: TabA(4 fields), TabC(4 fields).

if you run this query:

SELECT count(*) FROM information_schema.columns WHERE table_name = 'TabA' 

it will return 7 because there are two tables named TabA. But by adding another condition table_schema = 'SchemaNameHere':

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'DBaseA'   AND table_name = 'TabA' 

then it will only return 3.