Count the number of columns in a specific table

In this article, I will show how to count the number of columns of a table that exist in your database.
To explain this in detail, I will:
Step 1: Create a database named "test" as in the following:
  1. Create database test
Create a Database
Step 2: Create a table named "Employee" and "Department" in the "test" database as in the following:
  1. Create Table Employee
  2. (
  3. Emp_ID int Identity,
  4. Emp_Name varchar(20),
  5. Department_ID int
  6. )
  7. GO
  8. Create Table Department
  9. (
  10. Department_ID int ,
  11. Department_Name varchar(20)
  12. )
Create a table
Provide the answers of some questions:

How to get the list of all columns of the "Employee" table?

Solution: There is a system view named "columns" in every database by which you can get the list of every kind of entities that exist in your database. You can only access this view by the schema called "information_schema" like information_schema.columns.
information_schema.columns

Get the list of all columns of the SQL table

  1. select column_name from information_schema.columns where table_name='Employee'
list of all columns
Note: You can also use the "System Stored Procedure" to do this like:
  1. sp_help Employee

How to count the number of columns of the SQL table?

Count the number of all columns of the "Employee" table
  1. select count(column_name)as Number from information_schema.columns where table_name='Employee'
Count the number of all columns

How to get a single list of all columns in the Database?

To count, get a single list of all columns of "Employee" and "Department" in the "test" Database as in the following:
  1. select column_name,table_name as Number from information_schema.columns
single list of all columns