Add a column to a table, if it does not already exist

Question

I want to write a query for MS SQL Server that adds a column into a table. But I don't want any error display, when I run/execute the following query.

I am using this sort of query to add a table ...

IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[Person]')
              AND TYPE IN (N'U')
   )

But I don't know how to write this query for a column.

possible duplicate of [How to check if column exists in SQL Server table](http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table) — Martin Smith, Jan 15 '12 at 15:23
You should use `sys.tables` instead of the "generic" `sys.objects` - then you don't have to specify the type explicitly (it's obvious from the `sys.tables` already....) — marc_s, Jan 15 '12 at 15:44
COL_LENGTH Alternative only works from SQL-Server 2008, but it works. — Paul-Henri, Dec 13 '16 at 09:20
@MartinSmith very much NOT a duplicate of that. Your link is one possible way to solve it (and indeed, is the recommended way, right now). But the question is actually different and other solutions could be available (e.g. if SQL adds an `IF NOT EXISTS` clause to the `ADD COLUMN` syntax) — Brondahl, Sep 01 '20 at 14:53
@Brondahl - as the question has survived open in the 8.5 years since the comment you are replying to was posted probably no need to panic. At the moment all the answers here are basically dupes of the ones in the linked Q though — Martin Smith, Sep 01 '20 at 15:27

score 298 · Accepted Answer · edited May 02 '18 at 09:22

298

You can use a similar construct by using the sys.columns table io sys.objects.

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)

edited May 02 '18 at 09:22

Jitendra Pancholi

7,897
12
51
84

answered Jan 15 '12 at 15:24

Lieven Keersmaekers

57,207
13
112
146

49

Note that in this instance you want to use IF NOT EXISTS in your actual code. – Nat Apr 04 '13 at 02:27
4

For optimized query you can use top 1 with select statement – Banketeshvar Narayan Apr 01 '14 at 16:53
26

@BanketeshvarNarayan this is incorrect. The execution plans for subqueries in an `EXISTS` clause are identical. Things like `SELECT 1` or `SELECT TOP 1` are unnecessary. The `EXISTS` clause itself tells the query optimizer to only perform the minimum reads necessary to evaluate the `EXISTS`... at least in SQL Server. Other DB engines may have a more or less efficient query optimizer. – Kenneth Cochran Jul 21 '15 at 16:56
21

@BanketeshvarNarayan If you are optimizing your `ADD Column` queries... you must be running them too often! – Fenton Apr 27 '16 at 12:47
I'm confused after Nat's comment.. should we edit the above answer to reflect what he said? – user391339 Feb 11 '18 at 05:34
1

@user391339 - I have posted a similar construct as OP posted but yes, if you want to take action if the column *doesn't* exist, the statement would be IF NOT EXISTS. I don't really feel the need to edit the answer for this but do feel free to edit yourself if you think it's an improvement. – Lieven Keersmaekers Feb 11 '18 at 13:11
How would we check if two columns exist? – Kush Sep 20 '18 at 01:07
2

@Kush - Replace `AND = 'ColumnName'` with `AND name in ('ColumnName1', 'ColumnName2')` – Lieven Keersmaekers Sep 20 '18 at 06:18

score 143 · Answer 2 · edited May 25 '16 at 10:59

143

IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
    ALTER TABLE table_name
    ADD [column_name] INT
END

edited May 25 '16 at 10:59

Pedram

6,256
10
65
87

answered Jun 21 '13 at 12:04

SPL

1,431
1
9
2

1

I tried doing this one but it returns an error which states that COL_LENGTH function does not exist. – ThEpRoGrAmMiNgNoOb Nov 09 '15 at 05:35
6

Requires SQL Server 2008+ – Robert Brown Dec 16 '16 at 06:11
12

Small addition - one shouldn't use square brackets in the column name, since `COL_LENGTH('table_name', '[column_name]')` always return null in SQL Server 2016 (`COL_LENGTH('[table_name]', 'column_name') works as expected`). – stop-cran Sep 27 '17 at 11:05
that's because you are essentially quoting the string twice if you put brackets inside single quotes. – John Lord Dec 10 '21 at 19:49

score 43 · Answer 3 · edited May 25 '16 at 11:00

43

Another alternative. I prefer this approach because it is less writing but the two accomplish the same thing.

IF COLUMNPROPERTY(OBJECT_ID('dbo.Person'), 'ColumnName', 'ColumnId') IS NULL
BEGIN
    ALTER TABLE Person 
    ADD ColumnName VARCHAR(MAX) NOT NULL
END

I also noticed yours is looking for where table does exist that is obviously just this

 if COLUMNPROPERTY( OBJECT_ID('dbo.Person'),'ColumnName','ColumnId') is not null

edited May 25 '16 at 11:00

Pedram

6,256
10
65
87

answered Jan 15 '12 at 15:40

JStead

1,710
11
12

2

I like it. I think the best part about posting here is finding gems like this. – JStead Jan 15 '12 at 15:44
6

What is 'ColumnId'? – Marius Stănescu Dec 20 '19 at 07:46
'ColumnId' is the name of the column property you are checking against. You could have probably used any property name that exists on a column, such as name, etc. – Jacques Bosch Feb 02 '20 at 16:17

Adil H. Raza · Answer 4 · 2018-10-17T14:33:33.757

7

Here's another variation that worked for me.

IF NOT EXISTS (SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE upper(TABLE_NAME) = 'TABLENAME'
        AND upper(COLUMN_NAME) = 'COLUMNNAME')
BEGIN
    ALTER TABLE [dbo].[Person] ADD Column
END
GO

EDIT: Note that INFORMATION_SCHEMA views may not always be updated, use SYS.COLUMNS instead:

IF NOT EXISTS (SELECT 1 FROM SYS.COLUMNS....

edited Oct 17 '18 at 14:33

answered Oct 03 '18 at 12:49

Adil H. Raza

1,649
20
25

2

If you're going to edit just edit it to use SYS.COLUMNS not a note to say change the query! – Alan Macdonald Jul 08 '21 at 09:23

score 6 · Answer 5 · answered May 02 '19 at 07:14

6

IF NOT EXISTS (SELECT * FROM syscolumns
  WHERE ID=OBJECT_ID('[db].[Employee]') AND NAME='EmpName')
  ALTER TABLE [db].[Employee]
  ADD [EmpName] VARCHAR(10)
GO

I Hope this would help. More info

answered May 02 '19 at 07:14

ShaileshDev

1,086
13
16

2

This worked for me on SqlServer 2000 while the accepted answer didn't. The sys.* views appear to have been added somewhere around SqlServer 2005, cf. https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/system-compatibility-views-transact-sql – ZeRemz Dec 16 '19 at 13:20

score 2 · Answer 6 · answered Feb 11 '20 at 10:50

When checking for a column in another database, you can simply include the database name:

IF NOT EXISTS (
  SELECT * 
  FROM   DatabaseName.sys.columns 
  WHERE  object_id = OBJECT_ID(N'[DatabaseName].[dbo].[TableName]') 
         AND name = 'ColumnName'
)

score 1 · Answer 7 · answered Dec 01 '16 at 16:29

1

IF NOT EXISTS (SELECT 1  FROM SYS.COLUMNS WHERE  
OBJECT_ID = OBJECT_ID(N'[dbo].[Person]') AND name = 'DateOfBirth')
BEGIN
ALTER TABLE [dbo].[Person] ADD DateOfBirth DATETIME
END

answered Dec 01 '16 at 16:29

Code First

427
4
3

Add a column to a table, if it does not already exist

7 Answers7

Linked