Categories
sql-server sql-server-2008 sql-server-2012 sql-server-2016 tsql

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

2074

I need to add a specific column if it does not exist. I have something like the following, 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 the SQL Server database?

5

  • 15

    I don’t actually think there’s anything wrong with the code in the question: Works finely for me in 2008 R2. (Maybe you were running it in the wrong database? Maybe your database was case-sensitive and you didn’t have the case right in your myTableName / myColumnName strings? This type of query seems more flexible than the COL_LENGTH solution: I’m able to run it against a different database and even over a database link by suitably prefixing “INFORMATION_SCHEMA”. Couldn’t see how to do that with the COL_LENGTH metadata-function.

    – mwardm

    Jun 13, 2013 at 14:57


  • 4

    @mwardm – COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') works fine.

    Sep 12, 2013 at 16:38

  • 7

    Little related hint: if you want to update a column right after column addition(I believe many users were searching this article for that purpose), you could use EXEC sp_executesql with formed UPDATE statement.

    Apr 16, 2015 at 15:02

  • 1

    The real answer is you should add the database you are checking against so it’s FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS

    Jun 25, 2015 at 22:35

  • 1

    You can also use syscolumns and sysobjects very simply.

    – dcpking

    Aug 29, 2020 at 4:02

2265

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

9

  • 1

    In Martin Smith’s version, one thing to mention is not to include columnName within square brackets [ ]. When columnName is inside square brackets [ ], it’ll give null even if the column exists in the table

    – Hemendra

    Jun 24, 2019 at 7:57

  • @HemendraSinghChauhan – that’s because they aren’t part of the name. You will also find that when comparing with the name in sys.columns

    Jun 24, 2019 at 7:59

  • 1

    Shorter version not wotking when field id varchar(max) = null

    Aug 13, 2020 at 17:35

  • 2

    @AlejandroDG Can you provide an example? I would say, this claim is not true.

    – kapsiR

    Mar 9, 2021 at 15:46

  • 1

    @sur – So in what sense would that mean “AlejandroDG is right”? The predicate is IS NOT NULL not >0

    Apr 23 at 5:23

1104

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.

17

  • 13

    This is less readable than some of the other answers, probably why it’s not as highly rated.

    – Bill Yang

    Nov 30, 2011 at 22:09


  • 43

    @Bill – Less readable in what way? Looks fine in Firefox. This answer was posted more than 2 years later than the accepted one, which explains the rating IMO. If you meant less clear that it is an existence check this type of idiom is quite common in SQL Server. e.g. using IF OBJECT_ID('TableName','U') IS NULL to check object existence or DB_ID('foo') to check database existence.

    Nov 30, 2011 at 22:31

  • 65

    @MartinSmith I’m sure he meant less readable because if you didn’t know this idiom, and you inherited this code from someone else, you would not immediately understand what the code does. Kind of like writing x>>2 instead of x/4 in C++. The more verbose code (if exists (select column_name from information_schema ...)) takes a lot more space, but no one would ever scratch their head trying to figure out what it does.

    – Kip

    Aug 20, 2013 at 16:49

  • 27

    Besides more concise this is a way faster solution. Accessing INFORMATION_SCHEMA views or sys.columns hits disk, while COL_LENGTH uses cached database metadata.

    – wqw

    Jan 13, 2014 at 8:49

  • 9

    This is probably not the most highly rated answer because it was given 2.5 years after the other one. That’s why I always check the dates when comparing the ratings on two answers. It takes a lot longer to overcome an answer that was given much earlier. 😉

    – Sean

    Feb 28, 2014 at 19:35

170

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?

3

  • 6

    I just found out that adding TABLE_SCHEMA = ‘mySchema’ after where clause fixes the problem.

    – Maciej

    Sep 25, 2008 at 17:01

  • 13

    -1: does not answer OP’s question, only adds the new information on how to add a new collumn despite OP not asking about that at all, does not address OP’s comment.

    – ANeves

    Nov 2, 2011 at 11:46

  • 3

    +1 Answers OP’s question perfectly with a bonus of the additional information the OP was going for next anyways. And this was what I was looking for.

    Nov 11, 2019 at 11:07