How to check if a column exists in the SQL Server table

There are three common ways to check if column exists in the table

1. Check column in the SYS.COLUMNS
2. Check column in the INFORMATION_SCHEMA.COLUMNS
3. Use COL_LENGTH function

You can use any of these methods.

Check Column exists Example

Let’s check if the ‘DateOfBirth‘ column exists in the ‘dbo.Person‘ table.
As you can see from the screenshot all three SQL statements show that the ‘DateOfBirth’ column does not exist.

Use one of these statements to check if a column exists

--1. SYS.COLUMNS
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS
          WHERE [Name] = N'DateOfBirth' AND Object_ID = Object_ID(N'[YarkulTestDb1].[dbo].[Person]'))
BEGIN
    SELECT 'COLUMN DOES NOT EXISTS'
END
--2. INFORMATION_SCHEMA.COLUMNS
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'DateOfBirth') 
BEGIN
    SELECT 'COLUMN DOES NOT EXISTS'
END
--3. COL_LENGTH
IF COL_LENGTH('dbo.[Person]', 'DateOfBirth') IS NULL
BEGIN
    SELECT 'COLUMN DOES NOT EXISTS'
END

Additional Tip

If you are checking the column existence before dropping it, from SQL Server 2016 you can use the new DIE (Drop If Exists) statement

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

Video