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

Asked

Viewed 29,661 times

15

I am trying to add a new column to an 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 know if a column already exists in an SQL Server table?

sql sql-server

  • You can just try to enter, if it already exists it will simply give an error.

  • @Havenard is true, but I want a way to add it if it’s not there, but if it is, do nothing. So it will not give error, and I can apply the script several times and always exit in the same way (with the column).

5 answers

13


To system view INFORMATION_SCHEMA.COLUMNS is specific to each bank. Add the name of the bank to ensure that you are checking in the correct bank. Depending on the collation bank, another possible problem is with the box (upper case/minute).

Try the following:

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

10

To find out 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 to adapt 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

6

This way, one searches the column name in all Schema tables.

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE 'NomeColuna'

5

Try this, works for aSQL Server 2008+ and 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!'

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

4

One simple way I found is to check the 'greeting' (length) of the column - 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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.