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

sql-server, sql-server-2008, sql-server-2012, sql-server-2016, t-sql

Solution

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Problem

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?

Original source