Como verificar se existe uma coluna em uma tabela do SQL Server?

Eu preciso adicionar uma coluna específica se ela não existir. Eu tenho algo parecido com o seguinte, mas sempre retorna falso:

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

Como posso verificar se existe uma coluna em uma tabela do database do SQL Server?

SQL Server 2005 em diante:

 IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName' AND Object_ID = Object_ID(N'schemaName.tableName')) BEGIN -- Column Exists END 

A versão de Martin Smith é mais curta:

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

Uma versão mais concisa

  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 

O ponto sobre permissions na visualização de metadados se aplica a todas as respostas, não apenas a essa.

Observe que o primeiro nome da tabela de parâmetros para COL_LENGTH pode estar no formato de nome de uma, duas ou três partes, conforme necessário.

Um exemplo que faz referência a uma tabela em um database diferente é

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

Uma diferença com essa resposta, em comparação com o uso das exibições de metadados, é que as funções de metadados, como COL_LENGTH sempre retornam apenas dados sobre alterações confirmadas, independentemente do nível de isolamento em vigor.

Ajuste o abaixo para atender às suas necessidades específicas:

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

Edite para lidar com a edição para a pergunta : Isso deve funcionar – dê uma olhada cuidadosa no seu código para erros estúpidos; você está consultando o INFORMATION_SCHEMA no mesmo database em que sua inserção está sendo aplicada, por exemplo? Você tem um erro de digitação em seu nome de tabela / coluna em qualquer declaração?

Tente isso …

 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 

Eu prefiro INFORMATION_SCHEMA.COLUMNS em uma tabela do sistema porque a Microsoft não garante a preservação das tabelas do sistema entre as versões. Por exemplo, o dbo.syscolumns ainda funciona no SQL 2008, mas está obsoleto e pode ser removido a qualquer momento no futuro.

Você pode usar as visualizações do sistema de esquema de informações para descobrir praticamente qualquer coisa sobre as tabelas nas quais está interessado:

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

Você também pode interrogar modos de exibição, stored procedures e praticamente qualquer coisa sobre o database usando as exibições Information_schema.

Primeiro verifique se a combinação de table / column ( id / name ) existe em dbo.syscolumns (uma tabela interna do SQL Server que contém definições de campos) e, se não emitir a consulta ALTER TABLE apropriada para adicioná-la. Por exemplo:

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

Tente algo como:

 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 

Então use-o assim:

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

Ele deve funcionar tanto no SQL Server 2000 quanto no SQL Server 2005. Não tenho certeza sobre o SQL Server 2008, mas não vejo por que não.

Para as pessoas que estão verificando a existência da coluna para soltá-lo.

No SQL Server 2016, você pode usar novas instruções DIE em vez de grandes wrappers IF

 ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name 
 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 

Um bom amigo e colega meu me mostrou como você também pode usar um bloco IF com as funções SQL OBJECT_ID e COLUMNPROPERTY no SQL SERVER 2005+ para verificar uma coluna. Você pode usar algo semelhante ao seguinte:

Você pode ver por si mesmo aqui

 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 

Isso funcionou para mim no SQL 2000:

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

Tente isso

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

Eu precisava de similar para o SQL SERVER 2000 e, como aponta o @Mitch, isso só funciona em 2005+.

Deve ajudar alguém, foi o que funcionou para mim no final:

 if exists ( select * from sysobjects, syscolumns where sysobjects.id = syscolumns.id and sysobjects.name = 'table' and syscolumns.name = 'column') 
 if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='' and COLUMN_NAME='') begin print 'Column you have specified exists' end else begin print 'Column does not exists' end 
 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; 
 select distinct object_name(sc.id) from syscolumns sc,sysobjects so where sc.name like '%col_name%' and so.type='U' 

Uma versão da tabela temporária da resposta aceita :

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

A resposta de Wheat é boa, mas assume que você não possui pares de nomes de colunas / nomes de tabela idênticos em nenhum esquema ou database. Para torná-lo seguro para essa condição use isso …

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

Uma das soluções mais simples e compreensível é:

 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 

Aqui está um script simples que uso para gerenciar a adição de colunas no 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 

Neste exemplo, o Name é o ColumnName a ser adicionado e Object_Id é o TableName

Existem várias maneiras de verificar a existência de uma coluna. Eu recomendaria fortemente usar INFORMATION_SCHEMA.COLUMNS como ele é criado para se comunicar com o usuário. Considere as seguintes tabelas:

  sys.objects sys.columns 

e até mesmo alguns outros methods de access disponíveis para verificar o system catalog.

Além disso, não é necessário usar o SELECT * , basta testá-lo pelo NULL value

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

Ainda outra variação …

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