In this tutorial, we will share to you how to check available colum 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.

Step 1

First, let us create the dataset:

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

Step 2

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'

There is also a shorter way to do this. You can 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.

Simple and fast, right?

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.