How to check if a column exists in a SQL Server Table

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?

First check if the table/column(id/name) combination exists in dbo.syscolumns (an internal SQL Server table that contains field definitions), and if not issue the appropriate ALTER TABLE query to add it. For example:

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

Or

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

Or

Try something like:

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

Then use it like this:

IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
  ALTER TABLE xxx
  ADD yyyyy varChar(10) NOT NULL
END
GO
It should work on both SQL Server 2000 & SQL Server 2005. Not sure about SQL Server 2008, but don't see why not.

Or

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

SQL Insert depending if a column exists:

I need to do one INSERT or another depending if a column exist because of different versions of the same table.

I did the approach at this thread but SQL Server's pre check or 'sort of compilation' detects an error that would not fail during execution time.

Here's some code

IF COL_LENGTH('TableA', 'Column2') IS NOT NULL
BEGIN

INSERT INTO [dbo].[TableA]([Column1], [Column2], [Column3], [Column4])
SELECT value1, value2, value3, value4

END ELSE
BEGIN

INSERT INTO [dbo].[TableA]([Column1], [Column3], [Column4])
SELECT value1, value3, value4

END

Any workaround?

Answer:

SQL will know that the column doesn't exist so it won't let you run the query. The solution would be to execute a dynamic query.

DECLARE @value1 AS VARCHAR(50)
DECLARE @value2 AS VARCHAR(50)
DECLARE @value3 AS VARCHAR(50)
DECLARE @value4 AS VARCHAR(50)

SET @value1 = 'somevalue1'
SET @value2 = 'somevalue2'
SET @value3 = 'somevalue3'
SET @value4 = 'somevalue4'

DECLARE @SQL AS VARCHAR(MAX)

IF COL_LENGTH('TableA', 'Column2') IS NOT NULL
    BEGIN

        SET @SQL =
            'INSERT INTO [dbo].[TableA]([Column1], [Column2], [Column3], [Column4])
            SELECT ' + @value1 + ', ' + @value2 + ', ' + @value3 + ', ' + @value4
    END
ELSE
    BEGIN

        SET @SQL =
            'INSERT INTO [dbo].[TableA]([Column1], [Column3], [Column4])
            SELECT ' + @value1 + ', ' + @value3 + ', ' + @value4
    END

EXEC(@SQL).