Get the Count or List of Columns of a Table in SQL Server

In this article, I will show how to count the number of columns in a table that exists in your database.

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:
  • Create a database
  • Create a table with some columns
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