sql-serversql-server-2008t-sqlsql-server-2012sql-server-2016

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

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?

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