How To Count Number Of Columns In A Table In SQL Server

Being a senior developer in SQL Server, I have often been asked to count the number of columns in different tables in SQL server concerning performance optimization. I have identified a few simple approaches to do this. In this article, I will walk you through all those ways to count the number of columns in an SQL server.

How To Count Number Of Columns In A Table In SQL

Let us dive deep into the approaches below.

Approach-1 Using INFORMATION_SCHEMA.COLUMNS

The simplest way to get the total number of columns in a table in SQL is to use INFORMATION_SCHEMA.COLUMNS.

Syntax

SELECT COUNT(*) AS ColumnCount
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA = 'schema name';

Example

In the example below, I am trying to retrieve the total column count of the product table with the schema name dbo.

SELECT COUNT(*) AS ColumnCount
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Product' AND TABLE_SCHEMA = 'dbo';

After executing the above query, I got the expected output below.

Check out How To Get Table Count In SQL Server

Approach-2: Using sys.columns

We can also query the sys.columns to quickly get the total column count from a table using the below query.

Syntax

SELECT COUNT(*) AS ColumnCount
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YourTableName');

Example

In the example below, I am trying to retrieve the total column count of the AzureUSA table.

SELECT COUNT(*) AS ColumnCount
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.AzureUSA');

After executing the above query, I got the expected output, as shown in the screenshot below.

Conclusion

Counting columns in SQL Server tables is an essential skill I regularly use in my database management tasks. The above 2 simple approaches achieve this task.

You may also like following the articles below.

• How to Get Table Row Count in SQL Server
• How To Create A User In SQL Server Database
• Grant Write Access To Table In SQL Server
• How to Find Table Dependencies in SQL Server