How to check if a column exists in a SQL Server ta

Question

I need to add a specific column if it does not exist. I have something like the following, but it always returns false:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 

How can I check if a column exists in a table of the SQL Server database?

Answer 1

Wheat's answer is good, but assumes you do not have any identical table name / column name pairs in any schema or database. To make it safe for that condition use this...

select *
from Information_Schema.Columns
where Table_Catalog = 'DatabaseName'
  and Table_Schema = 'SchemaName'
  and Table_Name = 'TableName'
  and Column_Name = 'ColumnName'

Answer 2

Here is a simple script I use to manage addition of columns in the database:

IF NOT EXISTS (
        SELECT *
        FROM sys.Columns
        WHERE Name = N'QbId'
            AND Object_Id = Object_Id(N'Driver')
        )
BEGIN
    ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL
END
ELSE
BEGIN
    PRINT 'QbId is already added on Driver'
END

In this example, the Name is the ColumnName to be added and Object_Id is the TableName

Answer 3

For the people who is checking the column existence to drop it.

In SQL Server 2016 you can use new DIE statements instead of big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

Answer 4

A more concise version

IF COL_LENGTH('table_name','column_name') IS NULL
BEGIN
/* Column does not exist or caller does not have permission to view the object */
END

The point about permissions on viewing metadata applies to all answers not just this one.

Note that the first parameter table name to COL_LENGTH can be in one, two, or three part name format as required.

An example referencing a table in a different database is

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')

One difference with this answer compared to using the metadata views is that metadata functions such as COL_LENGTH always only return data about committed changes irrespective of the isolation level in effect.

Answer 5

There are several ways to check the existence of a column. I would strongly recommend to use INFORMATION_SCHEMA.COLUMNS as it is created in order to communicate with user. Consider following tables:

 sys.objects
 sys.columns

and even some other access methods available to check system catalog.

Also, no need to use SELECT *, simply test it by NULL value

IF EXISTS(
           SELECT NULL 
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE
             TABLE_NAME = 'myTableName'
             AND COLUMN_NAME = 'myColumnName'
         ) 

Answer 6

Yet another variation...

SELECT Count(*) AS existFlag FROM sys.columns 
WHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')