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

Count the number of columns of 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:

Create database test

 

Step 2: Create a table named "Employee" and "Department" in the "test" database as in the following:

Create Table Employee
(
        Emp_ID int Identity,
        Emp_Name varchar(20),
        Department_ID int
)
GO
Create Table Department
(
        Department_ID int          ,
        Department_Name varchar(20)
)

 

Provide the answers of some questions:

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

Question 2: How to count the number of all columns of the "Employee" table?

Question 3: How to get a single list of all columns of "Employee" and "Department" in the "test" Database?

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 databse. You can only access this view by the schema called "information_schema" like information_schema.columns.

 

Answer of Question 1: Get the list of all columns of the "Employee" table:

 

select column_name from information_schema.columns where table_name='Employee'

 

 

Note: You can also use the "System Stored Procedure" to do this like:

sp_help Employee

Answer of Question 2: Count the number of all columns of the "Employee" table:

select count(column_name)as Number from information_schema.columns where table_name='Employee'

 

Answer of Question 3: To count, get a single list of all columns of "Employee" and "Department" in the "test" Database as in the following:

select column_name,table_name as Number from information_schema.columns