SQL SERVER – How to Check if a Column Exists in SQL Server Table?

Question: How to check if a column exists in SQL Server table?

Answer: A fantastic question honestly. Here is a very simple answer for the question.

Option 1: Using Col_Length

I am using the following script for AdventureWorks database.

IF COL_LENGTH('Person.Address', 'AddressID') IS NOT NULL
    PRINT 'Column Exists'
ELSE
	PRINT 'Column doesn''t Exists'

Well, that is the answer of this question. It is very common for DBA to use above script when they want to add a new column with the script to any table.

Option 2: Using sys.columns

Here is another alternate script for the same. However, I prefer to use the option 1 most of the time.

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'AddressID'
          AND Object_ID = Object_ID(N'Person.Address'))
    PRINT 'Column Exists'
ELSE
	PRINT 'Column doesn''t Exists'

Option 3: Using Information_Schema

Here is another alternative to the above script with information_schema, which will pretty much work for SQL Server and many other RDBMS as well.

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

All of the above script give us exactly the same answer. Let me know if you use any other script which is handy and easy to use.

Reference: Pinal Dave (https://blog.sqlauthority.com)