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

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

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


当前回答

执行以下查询以检查给定表中是否存在该列:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';

其他回答

试试这个。。。

IF NOT EXISTS(
  SELECT TOP 1 1
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
    [TABLE_NAME] = 'Employees'
    AND [COLUMN_NAME] = 'EmployeeID')
BEGIN
  ALTER TABLE [Employees]
    ADD [EmployeeID] INT NULL
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')

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

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

试试这个

SELECT COLUMNS.*
FROM   INFORMATION_SCHEMA.COLUMNS COLUMNS,
       INFORMATION_SCHEMA.TABLES TABLES
WHERE  COLUMNS.TABLE_NAME = TABLES.TABLE_NAME
       AND Upper(COLUMNS.COLUMN_NAME) = Upper('column_name') 

执行以下查询以检查给定表中是否存在该列:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';