How to check if a column exists in SQL Server table

 24,117 Total Views,  20 views today

There are different ways to check whether specific column is exists in a sql server table

Solution:1
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
Print 'This Column is exists'
else
Print 'This Column doesn't exists'

Example:1
use BlogDB
go
IF COL_LENGTH('dbo.Customer', 'Customer_Name') IS NOT NULL
Print 'Customer_Name Column is exists in customer table '
else
Print 'Customer_Name Column doesn''t exists in customer table '
go

Solution:2
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = 'columnName'
AND Object_ID = Object_ID('schemaName.tableName'))
Print 'This Column is exists'
else
Print 'This Column doesn't exists'

Example:2
use BlogDB
go
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = 'Customer_Name'
AND Object_ID = Object_ID('dbo.Customer'))
Print 'Customer_Name Column is exists in customer table '
else
Print 'Customer_Name Column doesn''t exists in customer table '

Solution:3
IF EXISTS(
           SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'tableName'
           AND COLUMN_NAME = 'columnName'
         ) 

Example:3
use BlogDB
go
IF EXISTS(
           SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'Customer'
           AND COLUMN_NAME = 'Customer_Name'
         ) 
Print 'Customer_Name Column is exists in customer table '
else
Print 'Customer_Name Column doesn''t exists in customer table '