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)