sql-server - 如何检查SQL Server表中是否存在列？(How to check if a column exists in a SQL Server table?)

Question

I need to add a specific column if it does not exist.

(如果它不存在，我需要添加一个特定的列。)

I have something like the following, but it always returns false:

(我有类似以下内容，但始终返回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?

(如何检查SQL Server数据库表中是否存在列？)

  ask by Maciej translate from so

Answer 1

SQL Server 2005 onwards:

(从SQL Server 2005开始：)

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:

(Martin Smith的版本较短：)

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