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

I need to add a specific column if it does not exist. I have something like the following, but it always returns false:

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

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

How can I check if a column exists in a table of the SQL Server database?

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

23 个解决方案

#1

SQL Server 2005 onwards:

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

Martin Smith's version is shorter:

马丁·史密斯的版本更短:

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

#2

A more concise version

一个更简洁的版本

 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

The point about permissions on viewing metadata applies to all answers not just this one.

查看元数据的权限问题适用于所有答案，而不仅仅是这个答案。

Note that the first parameter table name to COL_LENGTH can be in one, two, or three part name format as required.

注意，COL_LENGTH的第一个参数表名可以按照需要使用1、2或3个部件名格式。

An example referencing a table in a different database is

引用另一个数据库中的表的示例是

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

One difference with this answer compared to using the metadata views is that metadata functions such as COL_LENGTH always only return data about committed changes irrespective of the isolation level in effect.

与使用元数据视图相比，此答案的一个不同之处在于，元数据函数(如COL_LENGTH)总是只返回提交更改的数据，而不考虑实际的隔离级别。

#3

Tweak the below to suit your specific requirements:

调整以下内容以适应您的具体要求:

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

Edit to deal with edit to question: That should work - take a careful look over your code for stupid mistakes; are you querying INFORMATION_SCHEMA on the same database as your insert is being applied to for example? Do you have a typo in your table/column name in either statement?

编辑处理编辑到问题:这应该是可行的-仔细检查你的代码，找出愚蠢的错误;您是否在应用insert的数据库上查询INFORMATION_SCHEMA ?您的表/列名中是否有输入错误?

#4

Try this...

试试这个…

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

#5

I'd prefer INFORMATION_SCHEMA.COLUMNS over a system table because Microsoft does not guarantee to preserve the system tables between versions. For example, dbo.syscolumns does still work in SQL 2008, but it's deprecated and could be removed at any time in future.

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

#6

You can use the information schema system views to find out pretty much anything about the tables you're interested in:

您可以使用信息模式系统视图来查找您感兴趣的表的任何信息:

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

You can also interrogate views, stored procedures and pretty much anything about the database using the Information_schema views.

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

#7

First check if the table/column(id/name) combination exists in dbo.syscolumns (an internal SQL Server table that contains field definitions), and if not issue the appropriate ALTER TABLE query to add it. For example:

首先检查dbo中是否存在表/列(id/name)组合。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

#8

Try something like:

尝试:

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

Then use it like this:

然后这样使用:

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

It should work on both SQL Server 2000 & SQL Server 2005. Not sure about SQL Server 2008, but don't see why not.

它应该同时适用于SQL Server 2000和SQL Server 2005。不确定SQL Server 2008，但不知道为什么。

#9

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

#10

For the people who is checking the column existence to drop it.

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

In SQL Server 2016 you can use new DIE statements instead of big IF wrappers

在SQL Server 2016中，您可以使用新的DIE语句而不是大IF包装器

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

#11

A good friend and colleague of mine showed me how you can also use an IF block with SQL functions OBJECT_ID and COLUMNPROPERTY in SQL SERVER 2005+ to check for a column. You can use something similar to the following:

我的一个好朋友和同事向我展示了如何在SQL SERVER 2005+中使用带有SQL函数OBJECT_ID和COLUMNPROPERTY的IF块来检查列。您可以使用以下内容:

You can see for yourself here

你可以在这里看到。

IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND
    COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL)
BEGIN
    SELECT 'Column does not exist -- You can add TSQL to add the column here'
END

#12

This worked for me in SQL 2000:

这在SQL 2000中对我有用:

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

#13

Try this

试试这个

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') 

#14

I needed similar for SQL SERVER 2000 and, as @Mitch points out, this only works inm 2005+.

我需要类似的SQL SERVER 2000，正如@Mitch指出的，这只适用于m2005 +。

Should it help anyone else, this is what worked for me in the end:

如果它能帮助任何人，这就是最终对我起作用的:

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

#15

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 exists'
  end

#16

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

#17

select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'

#18

A temp table version of the accepted answer:

接受答案的临时表版本:

if (exists(select 1 
             from tempdb.sys.columns  
            where Name = 'columnName'
              and Object_ID = object_id('tempdb..#tableName')))
begin
...
end

#19

Wheat's answer is good, but assumes you do not have any identical table name / column name pairs in any schema or database. To make it safe for that condition use this...

Wheat的答案很好，但是假设在任何模式或数据库中没有任何相同的表名/列名称对。为了在这种情况下安全使用这个…

select *
from Information_Schema.Columns
where Table_Catalog = 'DatabaseName'
  and Table_Schema = 'SchemaName'
  and Table_Name = 'TableName'
  and Column_Name = 'ColumnName'

#20

Here is a simple script I use to manage addition of columns in the database:

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

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

In this example, the Name is the ColumnName to be added and Object_Id is the TableName

在本例中，名称是要添加的ColumnName, Object_Id是TableName。

#21

One of the most simple and understandable solution is:

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

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

#22

There are several ways to check the existence of a column. I would strongly recommend to use INFORMATION_SCHEMA.COLUMNS as it is created in order to communicate with user. Consider following tables:

有几种方法可以检查列的存在性。我强烈建议使用INFORMATION_SCHEMA。为与用户通信而创建的列。考虑如下表:

 sys.objects
 sys.columns

and even some other access methods available to check system catalog.

甚至还有一些其他的访问方法可用来检查系统目录。

Also, no need to use SELECT *, simply test it by NULL value

同样，不需要使用SELECT *，只需使用NULL值对它进行测试

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

#23

Yet another variation...

另一个变化……

SELECT Count(*) AS existFlag FROM sys.columns 
WHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')