Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I need to add a specific column if it 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 on a table in SQL Server?

share|improve this question

17 Answers 17

up vote 743 down vote accepted

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

And an UPPER CASE version:

IF EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName'))
BEGIN
    -- Column Exists
END
share|improve this answer

A more concise version

 IF COL_LENGTH('table_name','column_name') IS NULL
 BEGIN
 /*Column does not exist or caller does not have permission to view the object*/
 END

The point about permissions on viewing metadata applies to all answers not just this one.

Note that the first parameter table name to COL_LENGTH can be in one, two, or three part name format as required.

An example referencing a table in a different database is

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')

One difference with this answer compared to using the metadata views is that metadata functions such as COL_LENGTH always only return data about committed changes irrespective of the isolation level in effect.

share|improve this answer

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

Edit to deal with edit to question: 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?

share|improve this answer

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
share|improve this answer

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.

share|improve this answer

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 2008, but it's deprecated and could be removed at any time in future.

share|improve this answer

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

Ity should work on both SS2000 & SS2005. Not sure about 2008, but don't see why not.

share|improve this answer

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
share|improve this answer
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
share|improve this answer

This worked for me in SQL 2000:

IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'table_name' and column_name = 'column_name')
begin
...
end
share|improve this answer

I needed similar for SQL SERVER 2000 and, as @Mitch points out, this only works inm 2005+.

Should it help anyone else, this is what worked for me in the end:

if exists (select * from sysobjects, syscolumns where sysobjects.id = syscolumns.id and sysobjects.name = 'table' and syscolumns.name = 'column')
share|improve this answer

Try this

SELECT COLUMNS.*
FROM INFORMATION_SCHEMA.COLUMNS COLUMNS, INFORMATION_SCHEMA.TABLES TABLES
WHERE COLUMNS.TABLE_NAME=TABLES.TABLE_NAME AND UPPER(COLUMNS.COLUMN_NAME)=UPPER('column_name')
share|improve this answer

A good friend and colleague of mine showed me how you can also use an IF block with SQL functions OBJECT_ID and COLUMNPROPERTY in SQL SERVER 2005+ to check for a column. You can use something similar to the following:

You can see for yourself here

IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND
    COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL)
BEGIN
    SELECT 'Column does not exist -- You can add TSQL to add the column here'
END
share|improve this answer
select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'
share|improve this answer
IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;
share|improve this answer
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='<table_name>' and COLUMN_NAME='<column_name>')
  begin
    print 'Column you have specified is exists'
  end
else
  begin
    print 'Column does not exists'
  end
share|improve this answer

Yet another variation...

SELECT Count(*) AS existFlag FROM sys.columns 
WHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')
share|improve this answer

protected by bummi Oct 13 at 11:02

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.10.25.1965\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["ion...\n\nSELECT Count(*) AS existFlag FROM sys.columns \nWHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')\nshare|improve this answer\n\nprotected by bummi Oct 13 at 11:02\n\nThank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.\n\nWould you like to answer one of these unanswered questions instead?\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}