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

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

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


当前回答

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Database Name'
and TABLE_SCHEMA = 'Schema Name'
and TABLE_NAME = 'Table Name'
and COLUMN_NAME = 'Column Name'
and DATA_TYPE = 'Column Type') -- Where statement lines can be deleted.

BEGIN
  -- Column exists in table
END

ELSE BEGIN
  -- Column does not exist in table
END

其他回答

接受答案的临时表格版本:

if (exists(select 1
           from tempdb.sys.columns
           where Name = 'columnName'
                 and Object_ID = object_id('tempdb..#tableName')))
begin
...
end
if exists (
  select * 
  from INFORMATION_SCHEMA.COLUMNS 
  where TABLE_NAME = '<table_name>' 
  and COLUMN_NAME = '<column_name>'
) begin
  print 'Column you have specified exists'
end else begin
  print 'Column does not exist'
end

我更喜欢INFORMATION_SCHEMA.COLUMNS而不是系统表,因为Microsoft不保证在不同版本之间保留系统表。例如,dbo.syscolumns在SQL Server 2008中仍然有效,但它已被弃用,将来可以随时删除。

我需要类似于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