SQL SERVER – Simple Way to Find Existence of Column in a Table

If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.

Let us create this dataset

USE TEMPDB;
CREATE TABLE TESTING(ID INT, NAME VARCHAR(100))

Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods
IF EXISTS
(
SELECT * FROM SYS.COLUMNS
WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING')
)
PRINT 'COLUMN EXISTS'
--
IF EXISTS
(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING'
)
PRINT 'COLUMN EXISTS'

But did you know there is a shorter way to do this? Use COL_LENGTH system function

IF (SELECT COL_LENGTH('TESTING','NAME')) IS NOT NULL
PRINT 'COLUMN EXISTS'

What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.

It is simple and faster.

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