How to Find Number of Columns in Table in MySQL

Sometimes database developers need to find number of columns in table in MySQL. This may be a programming or administrative requirement. There are several ways to do this. In this article, we will learn how to find number of columns in table in MySQL using information_schema database.

How to Find Number of Columns in Table in MySQL

Every MySQL installation has a database information_schema. It contains information about all tables, columns and schema present in your database server installation. This database is automatically created by MySQL at the time of installation and automatically updated whenever your databases are modified. It contains many tables, and among them COLUMNS table contains information about all columns in all databases in your MySQL server.

If your user has required permission, you will be able to query these tables using SQL queries, just as you query any other database tables.

Here is a simple query to get you a list of all columns, along with their table names in a database. Replace database_name and table_name below with the names of your database and table respectively.

SELECT column_name, table_name
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_catalog = 'database_name' 
   AND table_name = 'table_name';

If you simply want the count of columns in a table then use count(*) in your select query as shown below.

SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_catalog = 'database_name' 
   AND table_name = 'table_name';

If you want to find number of columns in a database, remove the clause table_name =’…’

SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_catalog = 'database_name' ;

In this article, we have learnt how to find number of columns in table in MySQL database.

Also read:

How to Search for Text in Every Field of MySQL database
How to Remove Leading & Trailing Whitespace from Column in MySQL
How to Remove Primary Key in MySQL
How to Find Most Frequent Column Value in MySQL
How to Truncate Foreign Key Constrained Table