How to check if column exists in SQL Server table

By : Maciej
Source: Stackoverflow.com
Question!

I need to add a specific column if it is does not exist. I have something like this, but it always returns false:

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

How can I check if a column exists in a table of SQL Server database?

By : Maciej


Answers

The commercial product I am working with uses a slightly more complicated query than the query below. The query and in particular the list of columns is created in the software.

SELECT COUNT(Column1+Column2+...+ColumnN)
FROM [Tablename]
WHERE ID=NULL

The query is presented to the server via software and an ODBC link to the database.

If the query runs okay, the columns exist. If the query fails, then further queries are executed and the missing columns created etc, so that the software always has the tables and columns it needs to run correctly.

The solution differs from many solutions above, which do handle SQL Server tables, but would be too restrictive for our product. Things like INFORMATION_SCHEMA.COLUMNS, sys.columns and object_id() may well prevent our software working on other databases like MySQL, etc.

On the other hand, our queries could crash an SQL script interpreter, so are unlikely to be suitable for everyone. If your queries are run by your own software and an ODBC connection to the SQL Server, and you want them to run with databases other than SQL Server too, then this approach might be worth consideration.



SQL Server 2005 onwards:

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


Use this query:

 IF EXISTS(SELECT Statement) 
    BEGIN
    Code
    END

    ELSE

    BEGIN
    Code
    END

    OR 

    IF NOT EXISTS( Select Statement)
    Begin
    code
    End

    ELSE
    Begin
    code
    End

This way you can use the conditions in SQL.

Hope it helps.

By : Nayan_07


This video can help you solving your question :)
By: admin