Find the Number of Columns in a MySQL Table

To find the number of columns in a MySQL table, use the count(*) function with information_schema.columns and the WHERE clause. Let us see an example.

Creating a table.

mysql> create table NumberOfColumns
   -> (
   -> id int,
   -> FirstName varchar(100),
   -> LastName varchar(100),
   -> Age int,
   -> Address varchar(100)
   -> );
Query OK, 0 rows affected (0.70 sec)

Inserting records into table.

mysql> insert into NumberOfColumns values(1,'Shane','Watson',36,'Australia');
Query OK, 1 row affected (0.15 sec)

mysql> insert into NumberOfColumns values(2,'Carol','Taylor',24,'US');
Query OK, 1 row affected (0.13 sec)

To display all records.

mysql> select *from NumberOfColumns;

Here is the output.

+------+-----------+----------+------+-----------+
| id   | FirstName | LastName | Age  | Address   |
+------+-----------+----------+------+-----------+
|    1 | Shane     | Watson   |   36 | Australia |
|    2 | Carol     | Taylor   |   24 | US        |
+------+-----------+----------+------+-----------+
2 rows in set (0.00 sec)

Now let us see the syntax to count the number of columns in a table.

SELECT count(*) AS anyName FROM information_schema.columns
WHERE table_name =' yourTableName';

Applying the above syntax in the example table with the name ?NumberOfColumns'.

mysql> SELECT count(*) AS NUMBEROFCOLUMNS FROM information_schema.columns
    -> WHERE table_name ='NumberOfColumns';

Here is the output.

+-----------------+
| NUMBEROFCOLUMNS |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

The alternate query to find the number of columns.

SELECT COUNT(*) AS anyName FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'yourDatabaseName' AND table_name = 'yourTableName';

Applying the second query to a database which has the name ?business'.

mysql> SELECT COUNT(*) AS NUMBEROFCOLUMNS FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE table_schema = 'business' AND table_name = 'NumberOfColumns';

The output displays the number of columns.

+-----------------+
| NUMBEROFCOLUMNS |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)