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

201

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.

This question is tagged with sql-server add not-exists

~ Asked on 2012-01-15 15:20:48

The Best Answer is


233

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

~ Answered on 2012-01-15 15:24:38


101

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

~ Answered on 2013-06-21 12:04:20


Most Viewed Questions: