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

213
votes

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.

7
sql-serveraddnot-exists
possible duplicate of How to check if column exists in SQL Server table - Martin Smith
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
COL_LENGTH Alternative only works from SQL-Server 2008, but it works. - Paul-Henri
@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
@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

7 Answers

247
votes

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'
)
114
votes
IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
    ALTER TABLE table_name
    ADD [column_name] INT
END
37
votes

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
5
votes

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

2
votes
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

0
votes
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
0
votes

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'
)