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

15

I'm trying to add a new column to 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 it always resolves as false.

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

    
asked by anonymous 02.02.2014 / 01:05

5 answers

12

The system view INFORMATION_SCHEMA.COLUMNS is specified for each bank. Add the name of the bank to make sure you are checking in the correct bank. Depending on the collation of the bank, another possible problem is with the box (upper / lower case).

Try the following:

IF EXISTS( SELECT * FROM MEUBANCO.INFORMATION_SCHEMA.COLUMNS 
            WHERE UPPER(TABLE_NAME) = UPPER('minhaTabela') 
            AND  UPPER(COLUMN_NAME) = UPPER('minhaColuna'))
    
02.02.2014 / 06:01
10

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 
    
02.02.2014 / 03:38
6

In this way, if you search the column name in all Schema tables.

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'NomeColuna' 
    
05.09.2014 / 19:23
5

Try this, it works for SQL Server 2008+ and it changes little to 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!'

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

    
03.02.2014 / 20:28
4

A simple way I found is by checking the column's 'length' - because 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
    
03.02.2014 / 19:01