SQL SERVER – How to Check if a Column Exists in SQL Server Table?

Question: How to check if a column exists in SQL Server table?

SQL SERVER - How to Check if a Column Exists in SQL Server Table? columnview-1-800x226

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)

, ,
Previous Post
SQL SERVER – How to Identify Columns Used In A View?
Next Post
SQL SERVER – Find Out Column Name Using COL_NAME() Function

Related Posts

2 Comments. Leave new

  • Many thx for the nice article

    Reply
  • Option 3 excludes the schema and to be equal to other options I think it should be:

    IF EXISTS
    (
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = ‘Person’
    AND table_name = ‘Address’
    AND column_name = ‘AddressID’
    )
    PRINT ‘Column Exists’
    ELSE
    PRINT ‘Column doesn”t Exists’

    Reply

Leave a Reply

Menu