declare @myColumn   as nvarchar(128)
set @myColumn = 'myColumn'
if not exists (
    select  1
    from    information_schema.columns columns 
    where   columns.table_catalog   = 'myDatabase'
        and columns.table_schema    = 'mySchema' 
        and columns.table_name      = 'myTable' 
        and columns.column_name     = @myColumn
    )
begin
    exec('alter table myDatabase.mySchema.myTable add'
    +'    ['+@myColumn+'] bigint       null')
end

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;

下面是我用来管理数据库中列添加的简单脚本：

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

在此示例中，Name是要添加的ColumnName，Object_Id是TableName

调整以下内容以满足您的具体要求：

if not exists (select
                     column_name
               from
                     INFORMATION_SCHEMA.columns
               where
                     table_name = 'MyTable'
                     and column_name = 'MyColumn')
    alter table MyTable add MyColumn int

这应该会奏效——仔细检查代码，找出愚蠢的错误；例如，您是否在应用插入的同一数据库上查询INFORMATION_SCHEMA？在这两个语句中，您的表/列名是否有拼写错误？

尝试以下操作：

CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
    SET @Result = 'T'
END
ELSE
BEGIN
    SET @Result = 'F'
END
RETURN @Result;
END
GO

GRANT EXECUTE ON  [ColumnExists] TO [whoever]
GO

然后这样使用：

IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
  ALTER TABLE xxx
  ADD yyyyy varChar(10) NOT NULL
END
GO

它应该可以在SQL Server 2000和SQL Server 2005上运行。我不确定SQL Server 2008，但我不知道为什么不这样做。

您可以一次检查SQLDB中的多个列，并返回一个字符串作为状态以检查列是否存在：

IF EXISTS
        (
          SELECT *
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'Table Name'
          AND(COLUMN_NAME = 'column 1'
          or COLUMN_NAME = 'column 2'
          or COLUMN_NAME = 'column 3'
          or COLUMN_NAME = 'column 4')
        )
        SELECT 'Column exists in table' AS[Status];
        ELSE
        SELECT 'Column does not exist in table' AS[Status];