如果不存在,我需要添加一个特定的列。我有类似以下的内容,但它总是返回false:

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

如何检查SQL Server数据库的表中是否存在列?


当前回答

IF NOT EXISTS(SELECT NULL
              FROM  INFORMATION_SCHEMA.COLUMNS
              WHERE table_name = 'TableName'
                    AND table_schema = 'SchemaName'
                    AND column_name = 'ColumnName') BEGIN

  ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0';

END;

其他回答

我需要类似于SQL Server 2000的东西,正如Mitch所指出的,这只适用于SQL Server 2005或更高版本。

这就是我最终的工作:

if exists (
    select *
    from
        sysobjects, syscolumns
    where
        sysobjects.id = syscolumns.id
        and sysobjects.name = 'table'
        and syscolumns.name = 'column')

这在SQL Server 2000中对我有效:

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table_name'
    AND column_name = 'column_name'
)
BEGIN
...
END

如果列不存在,请执行以下操作:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NULL)
    BEGIN
        // Do something
    END
END;

如果列确实存在,请执行以下操作:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NOT NULL)
    BEGIN
        // Do something
    END
END;

以下查询可用于检查表中是否存在搜索列。我们可以根据搜索结果做出决定,如下所示。

IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>)
  BEGIN
    SELECT 'Column Already Exists.'
  END
  ELSE
  BEGIN
    ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size]
  END

最简单易懂的解决方案之一是:

IF COL_LENGTH('Table_Name','Column_Name') IS NULL
  BEGIN
    -- Column Not Exists, implement your logic
  END
ELSE
  BEGIN
    -- Column Exists, implement your logic
  END