Как проверить, существует ли столбец в таблице SQL Server?

Мне нужно добавить определенный столбец, если он не существует. У меня есть что-то вроде следующего, но всегда возвращает false:

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

Как я могу проверить, существует ли столбец в таблице базы данных SQL Server?

На самом деле я не думаю, что с кодом в вопросе что-то не так: у меня отлично работает в 2008 R2. (Возможно, вы работали с неверной базой данных? Возможно, ваша база данных была чувствительна к регистру, и у вас не было правильного регистра в строках myTableName / myColumnName? Этот тип запроса кажется более гибким, чем решение COL_LENGTH: я могу чтобы запустить его для другой базы данных и даже для ссылки на базу данных с соответствующим префиксом "INFORMATION_SCHEMA". Не могу понять, как это сделать с помощью функции метаданных COL_LENGTH.
mwardm 13.06.2013 14:57:02
@mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate')отлично работает.
Martin Smith 12.09.2013 16:38:03
Небольшой связанный совет: если вы хотите обновить столбец сразу после добавления столбца (я полагаю, что многие пользователи искали эту статью для этой цели), вы можете использовать EXEC sp_executesqlс сформированным UPDATEутверждением.
cassandrad 16.04.2015 15:02:13
Реальный ответ заключается в том, что вы должны добавить базу данных, с которой вы проверяете, так что этоFROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS
Alex Kwitny 25.06.2015 22:35:00
30 ОТВЕТОВ
РЕШЕНИЕ

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
2038
12.04.2017 03:35:20
В версии Мартина Смита следует упомянуть, что не стоит включать columnName в квадратные скобки []. Когда ColumnName находится внутри квадратных скобок [], это даст нуль , даже если столбец существует в таблице
Hemendra 24.06.2019 07:57:37
@HemendraSinghChauhan - это потому, что они не являются частью имени. Вы также найдете это при сравнении с именем вsys.columns
Martin Smith 24.06.2019 07:59:29
@MartinSmith этого не знал, я использовал твой ответ и наткнулся на это. Обычно я использую квадратные скобки при добавлении столбцов, поэтому я использовал их и внутри функции COL_LENGTH. Мой код был таким:Alter table Table_Name Add [ColumnName] NVarchar(max) NULL; Select COL_LENGTH('[TABLE_NAME]', '[COLUMN_NAME]')
Hemendra 24.06.2019 08:08:44
да, это не верно Аргументы COL_LENGTHдолжны быть без кавычек. Теоретически возможно для кого-то создать столбец, который на самом деле имеет имя [COLUMN_NAME]- например, CREATE TABLE #T([[COLUMN_NAME]]] INT); SELECT * FROM #Tи тогда было бы двусмысленно, если бы это не было правилом.
Martin Smith 24.06.2019 08:12:24

Более краткая версия

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всегда, возвращают только данные о принятых изменениях, независимо от действующего уровня изоляции.

981
22.10.2018 07:19:27
Это менее читабельно, чем некоторые другие ответы, возможно, поэтому оно не так высоко оценено.
Bill Yang 30.11.2011 22:09:36
@ Билл - Менее читабельным, каким образом? Отлично смотрится в Firefox. Этот ответ был размещен более чем на 2 года позже принятого, что объясняет рейтинг IMO. Если вы имели в виду менее ясно, что это проверка существования, этот тип идиомы довольно распространен в SQL Server. например, используя IF OBJECT_ID('TableName','U') IS NULLдля проверки существования объекта или DB_ID('foo')для проверки существования базы данных.
Martin Smith 30.11.2011 22:31:28
@MartinSmith Я уверен, что он имел в виду менее читаемый, потому что, если вы не знали эту идиому и унаследовали этот код от кого-то другого, вы не сразу поняли бы, что делает код. Вроде как писать x>>2вместо x/4С ++. Более подробный код ( if exists (select column_name from information_schema ...)) занимает намного больше места, но никто бы никогда не почесал голову, пытаясь понять, что он делает.
Kip 20.08.2013 16:49:02
Кроме того, это более краткое решение. Доступ к INFORMATION_SCHEMAпросмотрам или sys.columnsпопаданиям на диск при COL_LENGTHиспользовании метаданных кэшированной базы данных
wqw 13.01.2014 08:49:11
Вероятно, это не самый высоко оцененный ответ, потому что он был дан через 2,5 года после другого. Вот почему я всегда проверяю даты при сравнении оценок по двум ответам. Требуется намного больше времени, чтобы преодолеть ответ, который был дан намного раньше. ;)
Sean 28.02.2014 19:35:42

Настройте ниже, чтобы удовлетворить ваши конкретные требования:

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 в той же базе данных, к которой применяется ваша вставка, например? У вас есть опечатка в названии таблицы / столбца в любом утверждении?

146
31.12.2014 08:57:33
Я только что узнал, что добавление TABLE_SCHEMA = 'mySchema' после предложения where решает проблему.
Maciej 25.09.2008 17:01:16
-1: не отвечает на вопрос OP, только добавляет новую информацию о том, как добавить новый столбец, несмотря на то, что OP вообще не спрашивает об этом, не отвечает на комментарий OP.
ANeves 2.11.2011 11:46:58
+1 Отвечает на вопрос ОП отлично с бонусом дополнительной информации, которую ОП продолжал в любом случае. И это было то, что я искал.
Bitterblue 11.11.2019 11:07:49

Попробуй это...

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
73
14.11.2011 07:18:51
Этот метод также работает с SQL CE, в то время как некоторые другие упомянутые методы этого не делают.
SWalters - Reinstate Monica 14.11.2013 20:00:24
Вы можете использовать SELECT 1вместо SELECT TOP 1 1;).
shA.t 15.06.2015 12:22:42
Внутри EXISTSоператора SQL автоматически оптимизирует удаленные столбцы (очень похоже count(*)), поэтому этого SELECT *будет достаточно.
Marc L. 1.03.2016 19:49:28
Для полноты вы должны рассмотреть возможность добавления and [TABLE_SCHEMA] = '???'в предложение WHERE.
Andrew Jens 6.11.2019 00:03:39

Я бы предпочел INFORMATION_SCHEMA.COLUMNSсистемную таблицу, потому что Microsoft не гарантирует сохранение системных таблиц между версиями. Например, dbo.syscolumnsвсе еще работает в SQL 2008, но он устарел и может быть удален в любое время в будущем.

46
26.06.2009 08:58:19
Ну да, это само собой разумеется, поскольку INFORMATION_SCHEMAпредставления содержат только метаданные стандарта ANSI. Однако этого достаточно для проверки существования.
Christian Hayter 26.02.2013 17:15:46
Microsoft говорит: «В будущих выпусках SQL Server Microsoft может расширить определение любого представления системного каталога, добавив столбцы в конец списка столбцов. Мы не рекомендуем использовать синтаксис SELECT * FROM sys.catalog_view_name в производственном коде, поскольку число возвращенные столбцы могут изменить и сломать ваше приложение. " Это означает, что они не будут удалять столбцы или менять их порядок. Это достаточно хорошая обратная совместимость для всех случаев, кроме крайних.
siride 12.07.2013 21:26:57

Для людей, которые проверяют существование столбца, чтобы бросить его.

С SQL Server 2016 вы можете использовать новые операторы DIE вместо больших IFоболочек

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
46
5.09.2019 04:08:25

Вы можете использовать системные представления информационной схемы, чтобы узнать практически все о таблицах, которые вас интересуют:

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

Вы также можете опросить представления, хранимые процедуры и почти все, что касается базы данных, используя представления Information_schema.

41
25.09.2008 12:38:35
Это именно то, что использует анкета, он должен был знать, как добавить столбец, если он не существует.
Birel 12.03.2020 13:46:41

Попробуйте что-то вроде:

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, но не понимаю, почему нет.

34
15.06.2015 12:27:43

Сначала проверьте, существует ли комбинация table/ column( id/ 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
34
15.06.2015 12:29:34

Один мой хороший друг и коллега показал мне, как можно также использовать IFблок с функциями SQL OBJECT_IDи COLUMNPROPERTYв SQL SERVER 2005+ для проверки столбца. Вы можете использовать что-то похожее на следующее:

Вы можете увидеть здесь

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
28
21.03.2013 17:27:42
И, конечно, если вы уверены, что таблица существует, вы можете пропустить первую часть условия и включить COLUMNPROPERTYтолько проверку .
Ruud Helderman 12.12.2014 12:52:15
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
26
3.03.2011 15:49:31

Это работало для меня в SQL 2000:

IF EXISTS 
(
    SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name = 'table_name' 
    AND column_name = 'column_name'
)
BEGIN
...
END
22
23.03.2017 19:31:12

Попробуй это

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') 
21
21.12.2014 14:35:54
Вам не нужно INFORMATION_SCHEMA.TABLESи вы не фильтруете столбцы для конкретной таблицы, поэтому иногда она возвращает более одной строки для одинаковых имен столбцов в отдельных таблицах;).
shA.t 15.06.2015 12:34:53

Мне нужно подобное для SQL SERVER 2000, и, как указывает @Mitch, это работает только в 2005+.

Должно ли это помочь кому-то еще, вот что в итоге сработало для меня:

if exists (
    select * 
    from 
        sysobjects, syscolumns 
    where 
        sysobjects.id = syscolumns.id 
        and sysobjects.name = 'table' 
        and syscolumns.name = 'column')
19
16.03.2017 07:30:10
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
15
7.03.2019 06:06:22
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;
13
17.09.2019 01:40:44
Я думаю, что вы имели в виду table_schema = 'имя_схемы'.
Tab Alleman 28.07.2014 13:17:48

Версия принятого ответа во временной таблице :

if (exists(select 1 
             from tempdb.sys.columns  
            where Name = 'columnName'
              and Object_ID = object_id('tempdb..#tableName')))
begin
...
end
11
23.05.2017 11:55:01
Чем это отличается от принятого ответа? Не будет ли временная таблица в принятом ответе?
John Saunders 8.01.2015 01:24:14
Правильный. Принятый ответ не работает для временных таблиц, потому что «sys.columns» должен быть указан как «tempdb.sys.columns», а имени таблицы должно предшествовать «tempdb ..».
crokusek 8.01.2015 20:08:26
select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'
10
6.12.2013 15:27:40

Ответ Уитя хороший, но предполагается, что в любой схеме или базе данных у вас нет идентичных пар имя таблицы / имя столбца. Чтобы сделать это безопасным для этого условия, используйте это ...

select *
from Information_Schema.Columns
where Table_Catalog = 'DatabaseName'
  and Table_Schema = 'SchemaName'
  and Table_Name = 'TableName'
  and Column_Name = 'ColumnName'
8
26.03.2015 17:42:36

Есть несколько способов проверить наличие столбца. Я настоятельно рекомендую использовать INFORMATION_SCHEMA.COLUMNSкак он создан для общения с пользователем. Рассмотрим следующие таблицы:

 sys.objects
 sys.columns

и даже некоторые другие методы доступа, доступные для проверки system catalog.

Кроме того, не нужно использовать SELECT *, просто проверьте егоNULL value

IF EXISTS(
           SELECT NULL 
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE
             TABLE_NAME = 'myTableName'
             AND COLUMN_NAME = 'myColumnName'
         ) 
8
28.04.2018 14:23:07
Независимо от того , даже если вы SELECT *с EXISTS, потому что , когда существует используются не реально выбрать все строки и все столбцы, внутренне он просто проверяет существование и фактически не проверяет все строки и столбцы
Pawan Nogariya 23.11.2018 08:55:56

Одним из самых простых и понятных решений является:

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
7
24.05.2017 07:17:40

Вот простой скрипт, который я использую для управления добавлением столбцов в базе данных:

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

7
28.01.2018 04:41:28

Приведенный ниже запрос может быть использован для проверки наличия или отсутствия в таблице искомого столбца. Мы можем принять решение на основе результатов поиска, как показано ниже.

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
4
30.10.2018 14:31:52

Еще один вариант ...

SELECT 
  Count(*) AS existFlag 
FROM 
  sys.columns 
WHERE 
  [name] = N 'ColumnName' 
  AND [object_id] = OBJECT_ID(N 'TableName')
3
7.03.2019 06:05:13

таблица -> таблица сценариев как -> новые окна - у вас есть сценарий дизайна. проверить и найти имя столбца в новых окнах

1
10.05.2019 15:56:44

Выполните приведенный ниже запрос, чтобы проверить, существует ли столбец в данной таблице:

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';
1
29.05.2019 13:43:52
IF EXISTS(SELECT 1 FROM sys.columns 
      WHERE Name = N'columnName'
      AND Object_ID = Object_ID(N'schemaName.tableName'))

Это должно быть довольно простым способом и прямым решением этой проблемы. Я использовал это несколько раз для похожих сценариев. Это работает как шарм, в этом нет сомнений.

0
3.04.2019 09:45:20
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 EXISTS IN TABLE
END
0
8.07.2019 10:58:47

Другим вкладом является следующий пример, который добавляет столбец, если он не существует.

    USE [Northwind]
    GO

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_NAME = 'Categories'
                        AND COLUMN_NAME = 'Note')
    BEGIN

    ALTER TABLE Categories ADD Note NVARCHAR(800) NULL

    END
    GO

Надеюсь, это поможет. Симона

0
11.07.2019 23:18:02

Сделайте что-нибудь, если столбец не существует:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NULL)
    BEGIN
        //Do something
    END
END;

Сделайте что-нибудь, если столбец существует:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NOT NULL)
    BEGIN
        //Do something
    END
END;
0
1.11.2019 11:53:11