How do I know if a column exists in a SQL Server table?

I'm trying to add a new column in a SQL Server table, and I want to know if it already exists or not. I have tried something like this: 

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

But always resolves as false.

How can I tell if a column already exists in a SQL Server table?

Author: bfavaretto, 2014-02-02
Source

5 answers

To system view INFORMATION_SCHEMA.COLUMNS it is specific to each bank. Add the bank name to make sure you're checking in the correct bank. Depending on the bank's collation , another possible problem is with the case (case).

Try the following: 

IF EXISTS( SELECT * FROM MEUBANCO.INFORMATION_SCHEMA.COLUMNS 
            WHERE UPPER(TABLE_NAME) = UPPER('minhaTabela') 
            AND  UPPER(COLUMN_NAME) = UPPER('minhaColuna'))
 13
Author: Wagner DosAnjos, 2017-09-08 17:11:19

To know if the column already exists, I thought of the following solution, with count: 

SELECT COUNT(COLUMN_NAME) AS resultado FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'minhaTabela' AND  COLUMN_NAME = 'minhaColuna'

In my understanding, this will return 0 if the column does not exist, and 1 if it exists. Try adapting something based on this example:)

EDIT : I found another example: 

DECLARE @retorno int

SELECT @retorno = COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'minhaTabela' AND  COLUMN_NAME = 'minhaColuna'

IF (@retorno > 0)
BEGIN
    --Tem a tabela
END
ELSE
BEGIN
    --Não tem a tabela
END
 10
Author: Calebe Oliveira, 2014-02-02 02:43:58

This way searches for the column name in all Schema tables.

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'NomeColuna'
 6
Author: fasr, 2014-09-05 17:23:13

Try This, It works for Asql Server 2008+ and changes little for previous versions.

 if exists(
  select *
  from sys.sysobjects so
  join sys.syscolumns sc on sc.id = so.id
  where so.xtype = N'U'
  and so.name like N'tb%'
  and sc.name like N'id%'
 )
 print 'EXISTE!'
 else
 print 'NON ECXISTEEEE!'
Break

You can use this select whenever you are looking for columns/tables in your schema

 5
Author: jean, 2014-02-03 19:28:39

A simple way I found is by checking the 'length' of the column - as the function returns NULL if the column does not exist. (This is for SQL Server 2005+) 

USE meuBanco
GO

IF ( COL_LENGTH( 'minhaTabela', 'minhaColuna' ) IS NOT NULL )
BEGIN

  -- Coluna existe

END
ELSE
BEGIN

  -- Coluna não existe

END
 4
Author: brazilianldsjaguar, 2014-02-03 18:01:09