题 如何检查SQL Server表中是否存在列?


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

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

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


1575
2017-09-25 12:34


起源


我实际上并不认为问题中的代码有任何问题:在2008 R2中为我做得很好。 (也许你是在错误的数据库中运行它?也许你的数据库是区分大小写的,你的myTableName / myColumnName字符串中没有这个案例?这种类型的查询似乎比COL_LENGTH解决方案更灵活:我能够通过适当地添加前缀“INFORMATION_SCHEMA”来对不同的数据库甚至数据库链接运行它。无法看到如何使用COL_LENGTH元数据函数执行此操作。 - mwardm
@mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') 工作正常。 - Martin Smith
几乎没有相关的提示:如果你想在添加列后立即更新列(我相信很多用户都是为了这个目的而搜索这篇文章),你可以使用 EXEC sp_executesql与形成 UPDATE 声明。 - cassandrad
真正的答案是你应该添加你正在检查的数据库,这样就可以了 FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS - Alex Kwitny


答案:


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

马丁史密斯的版本更短:

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

1729
2017-09-25 12:39



或者你可以节省一些打字和使用 IF COL_LENGTH('tableName', 'columnName') IS NOT NULL - Martin Smith
@Mitch - 根据文档,它将返回 NULL 如果用户没有权限查看元数据但是 sys.columns 也检查用户访问权限。 - Martin Smith
@MitchWheat - >我想说如果存在,而不是select * inside,我会使用sys.columns中的select 1,这样它只返回一个标量值,而不是一组行。你的想法 ? - vijaysylvester
这可能是显而易见的,但如果表的模式与dbo不同,则必须将模式作为名称的一部分进行编写: ... AND [object_id] = OBJECT_ID(N'schemaName.tableName' ...) 如果不这样做,即使列确实存在,也会导致查询不返回任何行。 - user1778770
@MitchWheat表示,你提供了1658人的错误推荐,而且他们中的大多数因为你而在他们的代码中造成了潜在的错误。您仍然没有回答,或者您检查了我提供的案例,如果清楚地显示,您的方法是错误的: oi63.tinypic.com/2mopgyc.jpg。它在通常实现的服务器权限层次结构的任何过程中都不起作用。所以,请停止蛊惑人心,并尝试在我的“解决方案”图片中提供的真实用例。然后说对不起,并编辑你的答案,停止向人们推荐越野车的方法。这是我最后的评论。有很好的评级。 - Juozas


更简洁的版本

 IF COL_LENGTH('table_name','column_name') IS NULL
 BEGIN
 /*Column does not exist or caller does not have permission to view the object*/
 END

关于查看元数据的权限的观点适用于所有答案,而不仅仅是这个答案。

注意第一个参数表名称为 COL_LENGTH 可以根据需要使用一个,两个或三个部分名称格式。

引用不同数据库中的表的示例是

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')

与使用元数据视图相比,这个答案的一个不同之处在于元数据功能,例如 COL_LENGTH 始终只返回有关已提交更改的数据,而不管有效的隔离级别如何。


854
2018-03-20 14:47



这比其他一些答案的可读性差,可能是为什么它的评价不高。 - Bill Yang
@Bill - 以什么方式可读性差?在Firefox中看起来很好。这个答案比公认的答案晚2年后发布,这解释了IMO的评级。如果你不太清楚它是否存在检查这种类型的习惯用法在SQL Server中很常见。例如运用 IF OBJECT_ID('TableName','U') IS NULL 检查对象存在或 DB_ID('foo') 检查数据库的存在。 - Martin Smith
@MartinSmith我确定他的意思不太可读,因为如果你不知道这个成语,并且你从别人那里继承了这个代码,你就不会立即理解代码的作用。有点像写作 x>>2 代替 x/4 在C ++中。代码越详细(if exists (select column_name from information_schema ...))需要更多的空间,但没有人会试图弄清楚它的作用。 - Kip
除了更简洁,这是一种更快的解决方案。访问 INFORMATION_SCHEMA 意见或 sys.columns 点击磁盘,而 COL_LENGTH 使用缓存的数据库元数据。 - wqw
这可能不是最受好评的答案,因为它是在另一个之后的2。5年。这就是为什么我总是在比较两个答案的评级时查看日期。克服早期给出的答案需要更长的时间。 ;) - Sean


调整以下内容以满足您的特定要求:

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,例如你的插入被应用?在任何一个语句中,您的表/列名称都有拼写错误吗?


125
2017-09-25 12:35



我刚刚发现在where子句修复问题后添加TABLE_SCHEMA ='mySchema'。 - Maciej
-1:没有回答OP的问题,只是添加了关于如何添加新列的新信息,尽管OP根本没有询问,但没有解决OP的评论。 - ANeves


尝试这个...

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

63
2017-09-25 12:35



此方法也适用于SQL CE,而提到的其他一些方法则不适用。 - Sandra Walters
您可以使用 SELECT 1 代替 SELECT TOP 1 1 )。 - shA.t
在一个 EXISTS 语句SQL自动优化列(很像 count(*))所以 SELECT * 就足够了。 - Marc L.


我更喜欢 INFORMATION_SCHEMA.COLUMNS 在系统表上,因为Microsoft不保证在版本之间保留系统表。例如, dbo.syscolumns 仍然可以在SQL 2008中使用,但它已被弃用,并且可以在将来的任何时候删除。


43
2018-06-26 08:58



“INFORMATION_SCHEMA视图可能不完整,因为它们未针对所有新功能进行更新” - onedaywhen
是的,从那以后就不用说了 INFORMATION_SCHEMA 视图仅包含ANSI标准元数据。但是,这对于存在测试来说已经足够了。 - Christian Hayter
微软称:“在未来的SQL Server版本中,微软可能会通过在列列表的末尾添加列来扩充任何系统目录视图的定义。我们建议不要在生产代码中使用语法SELECT * FROM sys.catalog_view_name,因为返回的列可能会更改并破坏您的应用程序。“这意味着他们不会删除列或更改其顺序。除了边缘情况之外,这对于除了边缘情况之外的所有后向兼 - siride


您可以使用信息架构系统视图来查找您感兴趣的表的几乎所有内容:

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'yourTableName'
 ORDER BY ORDINAL_POSITION

您还可以使用Information_schema视图查询视图,存储过程以及有关数据库的任何内容。


38
2017-09-25 12:37





先检查一下 table/columnid/name)组合存在于 dbo.syscolumns (包含字段定义的内部SQL Server表),如果没有,则发出相应的 ALTER TABLE 查询添加它。例如:

IF NOT EXISTS ( SELECT  *
            FROM    syscolumns
            WHERE   id = OBJECT_ID('Client')
                    AND name = 'Name' ) 
ALTER TABLE Client
ADD Name VARCHAR(64) NULL

28
2017-09-25 12:38





尝试以下方法:

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,但不明白为什么不。


26
2018-05-01 04:46





对于正在检查列存在的人来说。

SQL Server 2016 你可以使用新的DIE语句而不是大的 IF 包装

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

24
2018-03-03 15:49





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

22
2018-03-20 22:27