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

Question

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?

Solution 1

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

Solution 2

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.

Solution 3

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?

Solution 4

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

Solution 5

For the people who are checking the column existence before dropping it.

From SQL Server 2016 you can use new DIE (Drop If Exists) statements instead of big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

View more solutions

Comments

[mwardm]

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.

[Martin Smith]

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

[cassandrad]

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.

[Alex Kwitny]

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

[dcpking]

You can also use syscolumns and sysobjects very simply.

[Admin]

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

[Maciej]

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

[ANeves]

-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.

[Bill Yang]

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

[Martin Smith]

@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.

[onedaywhen]

"INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features"

[Christian Hayter]

Well yes, that goes without saying since INFORMATION_SCHEMA views contain only ANSI-standard metadata. However, that is sufficient for an existence test.

[siride]

Microsoft says "In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM sys.catalog_view_name in production code because the number of columns returned might change and break your application." That implies that they won't remove columns or change their order. That's good enough backward compatibility for all but edge cases.

[Kip]

@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.

[SWalters]

This method also works with SQL CE, whereas some of the other methods mentioned do not.

[wqw]

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.

[Sean]

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. ;)

[Pablo Claus]

"Less readable" problem solution: IF COL_LENGTH('Incidente_Incidente','Id_NC_I ') IS NULL /* <--- THIS line checks if column exists in SQL Server table */ ;)

[Tab Alleman]

I think you meant table_schema='schema_name'.

[Pasi Savolainen]

The select solution requires additional column check for schema scenario. col_length can be used col_length('schema.table', 'column').

[Martin Smith]

@PasiSavolainen and it accepts three part naming as well. db_name.schema_name.table_name. I'll edit my answer to include that explicitly as the three upvotes on this comment indicates that not everyone realises...

[Ruud Helderman]

And of course, if you are confident that the table exists, you can leave out the first part of the condition and check on COLUMNPROPERTY only.

[John Saunders]

How is that different from the accepted answer? Would a temp table not work in the accepted answer?

[crokusek]

Correct. The accepted answer does not work for temp tables because 'sys.columns' must be specified as 'tempdb.sys.columns' and the table name must be preceeded by 'tempdb..'.

[bvoyelr]

@Kip This is true, but in my experience the issue is mitigated by the code that inevitably follows: ALTER TABLE my_table ADD column_name :) Same with the OBJECT_ID method. If that code doesn't follow this use of the function, then perhaps some comments (or the expanded syntax in the original answer) is warranted.

[shA.t]

You can use SELECT 1 instead of SELECT TOP 1 1 ;).

[shA.t]

You don't need INFORMATION_SCHEMA.TABLES and you don't filter columns for a specific table, So it sometimes will return more than one row for same column names in separate tables ;).

[Mike]

Just needed to use this across our whole fleet - I can confirm this works against SQL 2000 through 2014.

[Marc L.]

Within an EXISTS statement SQL automatically optimizes the columns away (much like count(*)) so SELECT * will suffice.

[Pawan Nogariya]

No matter even if you SELECT * with EXISTS, because when exists is used it doesn't really select all the rows and all the columns, internally it just checks for the existence and not actually checks for all rows and columns

[Hemendra]

one thing is to make sure there's no square bracket [ ] in the column name as it gives null every time.

[Martin Smith]

@HemendraSinghChauhan - no it doesn't. CREATE TABLE dbo.Test([abc[]]123] int);select * from dbo.Test;SELECT COL_LENGTH('dbo.Test','abc[]123')

[Hemendra]

@MartinSmith what I meant was that SELECT COL_LENGTH('dbo.Test','[abc[]123]') will give NULL. Putting entire columnName in square brackets, in COL_LENGTH gives NULL.

[Hemendra]

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

[Martin Smith]

@HemendraSinghChauhan - that's because the open and closing square brackets aren't part of the name. You would also get the same thing if you tried SELECT * FROM sys.columns WHERE name = '[abc[]]123]'

[Martin Smith]

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

[Hemendra]

@MartinSmith didn't knew that, I was using your answer and came across this. Generally I use square brackets during adding columns, so I used them inside COL_LENGTH function too. My code was like this: Alter table Table_Name Add [ColumnName] NVarchar(max) NULL; Select COL_LENGTH('[TABLE_NAME]', '[COLUMN_NAME]')

[Martin Smith]

yes that isn't valid. The arguments to COL_LENGTH need to be unquoted. It is theoretically possible for someone to create a column that does actually have a name of [COLUMN_NAME] - e.g. CREATE TABLE #T([[COLUMN_NAME]]] INT); SELECT * FROM #T and then it would be ambiguous if this was not the rule.

[ob213]

'table_name' should be replaced with 'Schema_name.table_name'

[Andrew Jens]

For the sake of completeness, you should consider adding and [TABLE_SCHEMA] = '???' to the WHERE clause.

[Bitterblue]

+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.

[Birel]

This is exactly what the questionnaire is using, he needed to know how to add the column if it did not exist.

[AlejandroDG]

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

[kapsiR]

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

[BVernon]

@Wodzu I don't know what the answer list looked like at the time it was posted, but now Martin's answer is over a dozen answers down from this accepted answer. I would not have gone that far down to find it.

[BVernon]

@PabloClaus Having to put that comment sort of defeats the purpose of being more concise, and I really don't care about whether it's faster as someone else mentioned... I can't imagine any real scenario in the world where that's not negligible because no one is going to write this sort of statement in a loop. But I love being concise so I will totally use this is projects where I am in the one in charge. Will not use when working on other's projects though because I can already here the response "well this isn't consistent with the rest of our application" and that's basically the trump card.

[sur]

AlejandroDG is right. For nvarchar(max): docs.microsoft.com/en-us/sql/t-sql/functions/… "For varchar columns declared with the max specifier (varchar(max)), COL_LENGTH returns the value -1."

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? How is it different from the previous answers? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? How is it different from the previous answers? What was it tested on (versions, etc.)? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? How is it different from the previous answers? What was it tested on (versions, etc.)? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

Variation of what? Another answer? Mike Wheat's? Or independent?

[Peter Mortensen]

An explanation would be in order. E.g., what is the idea/gist? How is it different from the previous answers? What was it tested on (versions, etc.)? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

That is how it should be. With an explanation, not just a "try this" answer.

[Peter Mortensen]

But there isn't a "DIE" in there(?). What is the explanation? Preferably, please clarify it by changing the answer, not here in comments (but without "Edit:", "Update:", or similar - the answer should appear as if it was written today). Independent, can you link to documentation?

[Peter Mortensen]

Something seems to be missing here, at least the context. "Table" in what context? Inside SSMS? Something else? What do you mean by "you have design script" (seems incomprehensible)? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

[Peter Mortensen]

Why is there a space after "Column"?

[Martin Smith]

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

[Jeff Moden]

@MartinSmith - This should be the #1 answer, IMHO. BWAAA-HAAA-HAAA.... As for the "less readable" comments, if someone who doesn't know this method does and can't figure it out almost instantly when they first come across it insitu, then I'm not sure I'd want them to be the one to do anything with the code. ;)