How To Check If Column Exists In SQL Server Database

by

Recently, my team was tasked with implementing a new feature in one of our products that requires adding a new column to an existing table in our SQL database. As a research, we have identified a few best approaches to do this. In this article, we will discuss 3 simple approaches to checking whether a column exists in a table in the SQL server.

To check if a column exists in the SQL Server database, use COL_LENGTH (), sys.columns, and INFORMATION_SCHEMA.COLUMNS.

I have a table named Vehicle with 4 columns: VehicleID, VehicleName, VehicleBrand, and VehicleType.

sql check if column exists in select statement

Approach-1: Using COL_LENGTH ()

We can execute the below SQL query with the COL_LENGTH ().

IF COL_LENGTH('dbo.Vehicle', 'VehicleName') IS NOT NULL
PRINT 'Column Name Exists'
ELSE
PRINT 'Column Name doesn't Exist'

After executing the above query, which checks whether the column name VehicleName inside the Vehicle table exists, I got the expected output as “Column Name Exists.” Check out the screenshot below for your reference.

How to check if column exists in SQL Server database

Check out How to change column name in SQL Server

Approach-2: Using sys.columns

We can also execute the SQL query with the sys.columns.

IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'VehicleName'
AND Object_ID = Object_ID(N'dbo.Vehicle'))
PRINT 'Column Name Exists'
ELSE
PRINT 'Column Name doesn''t Exists'

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

sql server check if column exists

Check out How to check if table exists in SQL Server

Approach-3: Using INFORMATION_SCHEMA.COLUMNS

We can also execute the SQL query with the INFORMATION_SCHEMA.COLUMNS for this purpose.

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Vehicle'
    AND column_name = 'Name'
)
PRINT 'Column Name Exists'
ELSE
    PRINT 'Column Name doesn''t Exists'

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

sql server check if column exists in table

Conclusion

You can use SQL queries using COL_LENGTH (), sys.columns, and INFORMATION_SCHEMA.COLUMNS to check if a column exists in the SQL server. Use the specific method based on your convenience.

You can also like following the articles below