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

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:

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:

 

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.

 

 

 

Get the list of all columns of the SQL 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

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

 

Count the number of all columns of the "Employee" table

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

 

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:

select column_name,table_name as Number from information_schema.columns