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

SQL Server 2005 onwards:

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

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Tweak the below to suit your specific requirements:

if not exists (select
                     column_name
               from
                     INFORMATION_SCHEMA.columns
               where
                     table_name = 'MyTable'
                     and column_name = 'MyColumn')
    alter table MyTable add MyColumn int

That should work - take a careful look over your code for stupid mistakes; are you querying INFORMATION_SCHEMA on the same database as your insert is being applied to for example? Do you have a typo in your table/column name in either statement?

Try this...

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

I'd prefer INFORMATION_SCHEMA.COLUMNS over a system table because Microsoft does not guarantee to preserve the system tables between versions. For example, dbo.syscolumns does still work in SQL Server 2008, but it's deprecated and could be removed at any time in future.

You can use the information schema system views to find out pretty much anything about the tables you're interested in:

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'yourTableName'
 ORDER BY ORDINAL_POSITION

You can also interrogate views, stored procedures and pretty much anything about the database using the Information_schema views.

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 and SQL Server 2005. I am not sure about SQL Server 2008, but I don't see why not.

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

Do something if the column does not exist:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NULL)
    BEGIN
        // Do something
    END
END;

Do something if the column does exist:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NOT NULL)
    BEGIN
        // Do something
    END
END;

Another contribution is the following sample that adds the column if it does not exist.

    USE [Northwind]
    GO

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'Categories'
                        AND COLUMN_NAME = 'Note')
    BEGIN

    ALTER TABLE Categories ADD Note NVARCHAR(800) NULL

    END
    GO

The below query can be used to check whether searched column exists or not in the table. We can take a decision based on the searched result, also as shown below.

IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>)
  BEGIN
    SELECT 'Column Already Exists.'
  END
  ELSE
  BEGIN
    ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size]
  END

You can check multiple columns in SQLDB at once and return a string as status to check if columns exist:

IF EXISTS
        (
          SELECT *
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'Table Name'
          AND(COLUMN_NAME = 'column 1'
          or COLUMN_NAME = 'column 2'
          or COLUMN_NAME = 'column 3'
          or COLUMN_NAME = 'column 4')
        )
        SELECT 'Column exists in table' AS[Status];
        ELSE
        SELECT 'Column does not exist in table' AS[Status];

Execute the below query to check if the column exists in the given table:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Database Name'
and TABLE_SCHEMA = 'Schema Name'
and TABLE_NAME = 'Table Name'
and COLUMN_NAME = 'Column Name'
and DATA_TYPE = 'Column Type') -- Where statement lines can be deleted.

BEGIN
  -- Column exists in table
END

ELSE BEGIN
  -- Column does not exist in table
END

IF EXISTS(SELECT 1 FROM sys.columns
      WHERE Name = N'columnName'
      AND Object_ID = Object_ID(N'schemaName.tableName'))

This should be the fairly easier way and straightforward solution to this problem. I have used this multiple times for similar scenarios. It works like a charm, no doubts on that.

Table → script table as → new windows - you have design script.

Check and find the column name in the new windows.