2203

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?

Improve this question
5
  • 18
    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. Commented Jun 13, 2013 at 14:57
  • 4
    @mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') works fine. Commented 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. Commented Apr 16, 2015 at 15:02
  • 2
    The real answer is you should add the database you are checking against so it's FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS Commented Jun 25, 2015 at 22:35
  • 1
    You can also use syscolumns and sysobjects very simply. Commented Aug 29, 2020 at 4:02

33 Answers 33

Reset to default
1
2
2397

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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
@HemendraSinghChauhan - that's because they aren't part of the name. You will also find that when comparing with the name in sys.columns
@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]')
@AlejandroDG Can you provide an example? I would say, this claim is not true.
@sur - So in what sense would that mean "AlejandroDG is right"? The predicate is IS NOT NULL not >0
|
1177

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.

Improve this answer

19 Comments

This is less readable than some of the other answers, probably why it's not as highly rated.
@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.
@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.
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.
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. ;)
|
180

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?

Improve this answer

4 Comments

I just found out that adding TABLE_SCHEMA = 'mySchema' after where clause fixes the problem.
-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.
+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.
I mean, this answer does not feel useful to me, because I can not see significant differences between this and the question. Changing if exists to if not exists, changing * to column_name, and adding the then branch with alter table …, neither of these seem significant. If there is something else in the answer that I missed, maybe the specific difference/suggestion could be written explicitly? HTH
92

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
Improve this answer

4 Comments

This method also works with SQL CE, whereas some of the other methods mentioned do not.
You can use SELECT 1 instead of SELECT TOP 1 1 ;).
Within an EXISTS statement SQL automatically optimizes the columns away (much like count(*)) so SELECT * will suffice.
For the sake of completeness, you should consider adding and [TABLE_SCHEMA] = '???' to the WHERE clause.
88

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
Improve this answer

2 Comments

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?
Is it possible to do the opposite of this? like ALTER TABLE table_name ADD COLUMN IF NOT EXISTS ?
50

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.

Improve this answer

3 Comments

Well yes, that goes without saying since INFORMATION_SCHEMA views contain only ANSI-standard metadata. However, that is sufficient for an existence test.
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.
44

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.

Improve this answer

1 Comment

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

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.

Improve this answer

Comments

34

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
Improve this answer

Comments

30

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 and later 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
Improve this answer

1 Comment

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.
26 
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
Improve this answer

1 Comment

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

This worked for me in SQL Server 2000:

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table_name' AND column_name = 'column_name' ) BEGIN ... END
Improve this answer

Comments

21

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')
Improve this answer

1 Comment

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

One of the simplest and understandable solutions is:

IF COL_LENGTH('Table_Name','Column_Name') IS NULL BEGIN -- Column Not Exists, implement your logic END ELSE BEGIN -- Column Exists, implement your logic END
Improve this answer

Comments

18

I needed something similar for SQL Server 2000 and, as Mitch points out, this only works in SQL Server 2005 or later.

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')
Improve this answer

Comments

16 
IF NOT EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TableName' AND table_schema = 'SchemaName' AND column_name = 'ColumnName') BEGIN ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0'; END;
Improve this answer

1 Comment

I think you meant table_schema='schema_name'.
14 
if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '<table_name>' and COLUMN_NAME = '<column_name>' ) begin print 'Column you have specified exists' end else begin print 'Column does not exist' end
Improve this answer

Comments

12

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;
Improve this answer

1 Comment

Why is there a space after "Column"?
12

A temporary table version of the accepted answer:

if (exists(select 1 from tempdb.sys.columns where Name = 'columnName' and Object_ID = object_id('tempdb..#tableName'))) begin ... end
Improve this answer

2 Comments

How is that different from the accepted answer? Would a temp table not work in the accepted answer?
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..'.
9 
select distinct object_name(sc.id) from syscolumns sc,sysobjects so where sc.name like '%col_name%' and so.type='U'
Improve this answer

Comments

9

There are several ways to check the existence of a column. I would strongly recommend to use INFORMATION_SCHEMA.COLUMNS as it is created in order to communicate with user. Consider following tables:

 sys.objects sys.columns

and even some other access methods available to check system catalog.

Also, no need to use SELECT *, simply test it by NULL value

IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName' )
Improve this answer

1 Comment

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
9

Wheat's answer is good, but it assumes you do not have any identical table name / column name pairs in any schema or database. To make it safe for that condition, use this...

select * from Information_Schema.Columns where Table_Catalog = 'DatabaseName' and Table_Schema = 'SchemaName' and Table_Name = 'TableName' and Column_Name = 'ColumnName'
Improve this answer

1 Comment

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

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
Improve this answer

Comments

6

Here is a simple script I use to manage addition of columns in the database:

IF NOT EXISTS ( SELECT * FROM sys.Columns WHERE Name = N'QbId' AND Object_Id = Object_Id(N'Driver') ) BEGIN ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL END ELSE BEGIN PRINT 'QbId is already added on Driver' END

In this example, the Name is the ColumnName to be added and Object_Id is the TableName

Improve this answer

Comments

3

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
Improve this answer

Comments

2

Yet another variation...

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

2 Comments

Variation of what? Another answer? Mike Wheat's? Or independent?
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).
2

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];
Improve this answer

Comments

1

INFORMATION_SCHEMA work with current database, you must first call "Use [MyDatabase]"

Improve this answer

Comments

1

A simply way to get a result from external program with a query is that:

Select case when COL_LENGTH('table_name','column_name') IS NULL then 0 else 1 end as Result;
Improve this answer

Comments

0

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';
Improve this answer

Comments

1
2

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.