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)

Solarwinds
Previous Post
SQL SERVER – How to Identify InMemory Objects Can be Identified in SQL Server?
Next Post
Interview Question of the Week #042 – How Does SPACE Function Works in SQL Server?

Related Posts

No results found

2 Comments. Leave new

  • Giancarlo Gomez
    October 24, 2015 8:26 am

    You can simply do this as well:

    IF COL_LENGTH(‘TESTING’,’NAME’) IS NOT NULL
    PRINT ‘COLUMN EXISTS’

    No need for the additional SELECT

    Reply
  • Giancarlo Gomez
    October 24, 2015 8:27 am

    You can actually do the following without the need of the additional SELECT and it works the same:

    IF COL_LENGTH(‘TESTING’,’NAME’) IS NOT NULL
    PRINT ‘COLUMN EXISTS’

    Reply

Leave a Reply

Menu