How to get column count in SQL?

Question

Can anyone tell me how to get column count in SQL?

Answer 1

Here is the SQL query to count the number of columns in Employee table:

SELECT count (column_name) as Number 

FROM information_schema.columns 

WHERE table_name='Employee' 

Here is the SQL query to get the list of all the columns of Employee table:

SELECT column_name,table_name as Number 

FROM information_schema.columns

WHERE table_name = ‘Employee’

Here is the SQL query to get the list of all the columns of tables in the database:

SELECT column_name,table_name as Number 

FROM information_schema.columns

If you want to learn SQL and writing SQL queries, then check out this SQL Course by Intellipaat that provides Instructor-led training, certification, and also job assistance.

Answer 2

Some SQL commands, including ‘CREATE’ or ‘DROP’, cannot be executed without knowledge of the database architectural design of its tables. Therefore, to determine the count of columns in a specific table one should check the INFORMATION_SCHEMA.COLUMNS view and/or the sys.columns catalog in SQL Server. These two processes give you the ability to limit the output to the required table and specify the total number of columns in it. This comes in handy when the designer has to be concerned about the already existing tables within the system and their specific structures.

SELECT COUNT(*) AS ColumnCount

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'your_table_name';

SELECT COUNT(*) AS ColumnCount

FROM sys.columns

WHERE object_id = OBJECT_ID('your_table_name');