There are three common ways to check if column exists in the table
- Check column in the SYS.COLUMNS
- Check column in the INFORMATION_SCHEMA.COLUMNS
- 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