SQL Query to Find the Number of Columns in a Table

SQL stands for a structure query language, which is used in the database to retrieve data, update and modify data in relational databases like MySql, Oracle, etc. And a query is a question or request for data from the database, that is if we ask someone any question then the question is the query. Similarly when we want any data from the database then we write the query in SQL to get that data. In this article, we are talking about how we can find the number of columns present in a table.

Creating database :

To create a database there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query is,

create database database_name;

For example, 

create database GeeksforGeeks;

Output :

Commands completed successfully

Using the database :

To use the database there is a query we need to use in the SQL portal, like MySql, Oracle, etc. The  query is,

use database_name;

Here the query will be, 

use GeeksforGeeks;

Output :

Commands completed successfully

Add tables in the database :

To create tables in a database there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query  is,

create table table_name(
column1 type(size),
column2 type(size),
.
.
.
columnN type(size)
);

For example,

create table GeeksforGeeks(
course_ID INT,
course_name VARCHAR(50),
course_mentor VARCHAR(30),
course_fee INT,
course_start_date DATE
course_enrolled_student INT
);

Here, the table has 6 columns in it.

See the table :

To see the table use the ‘DESC table_name‘ query.

here the query is, 

desc geeksforgeeks;

If we use a Microsoft SQL server then we need to use ‘EXEC sp_help’ in place of DESC. In the Microsoft SQL server, the DESC command is not an SQL command, it is used in Oracle.

EXEC sp_help GFG_salary;

Output :

SL No   |    Column_name                   |    Type    |   Computed   |   Length
----------------------------------------------------------------------------------
1.        |    course_ID                       |    int     |      no      |     4
2.        |    course_name                   |   varchar  |      no      |     50 
3.        |    course_mentor                 |   varchar  |      no      |     30
4.        |    course_fee                       |     int    |      no      |     4
5.        |    course_start_date             |    date    |      no      |     3
6.        |    course_enrolled_student       |     int    |      no      |     4
----------------------------------------------------------------------------------

Add value into the table :

To add value to the table there is a query we need to use in the SQL platform, like MySql, Oracle, etc. The query is,

insert into table_name(
value1,
value2,
value3
.
.
.

valueN);

For example, here the query will be,

INSERT INTO `geeksforgeeks` (`course_ID`, `course_name`, `course_mentor`, `course_fee`, `course_start_date`, `course_enrolled_student`) VALUES
(1, 'SQL', 'mentor1', '3000', '2021-03-02', '10'),
(2, 'JAVA', 'mentor2a', '5000', '2021-03-02', '12'),
(3, 'DSA', 'mentor3', '4500', '2021-03-02', '25'),
(4, 'Python', 'mentor4', '3500', '2021-03-02', '20');

Output :

4 rows affected

Data present in the table after insertion :

select * from geeksforgeeks;

Output :

   | course_ID    | course_name | course_mentor | course_fee | course_start_date |  course_enrolled_student  
----------------------------------------------------------------------------------------------------------
1. |  1         |    SQL      |     mentor1   |  3000      | 2021-03-02        |  10
2. |  2         |    JAVA     |     mentor2a  |  5000      | 2021-03-02        |  12   
3. |  3         |    DSA      |     mentor3   |  4500      | 2021-03-02        |  25
4. |  4         |    Python   |     mentor4   |  3500      | 2021-03-02        |  20
----------------------------------------------------------------------------------------------------------

Now we have to find the number of columns present in the table,

To find that we can use a simple function COUNT() with INFORMATION_SCHEMA view in a relational database. This INFORMATION_SCHEMA is an ANSI standard set of views that provides read-only access to the details of databases and their objects like tables, constraints, procedures, etc. See the below query

SELECT count(*) as No_of_Column FROM information_schema.columns WHERE table_name ='geeksforgeeks';

Here, COUNT(*) counts the number of columns returned by the INFORMATION_SCHEMA .columns one by one and provides the final count of the columns. Here table_name selects the table in which we wish to work.

Output :

   |  No_of_Column  
-------------------
1. |  6
-------------------

So here, the final output will be 6, because in the table “geeksforgeeks” there are 6 columns present.