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

sql-server add not-exists
Tavousi picture Tavousi · Jan 15, 2012 · Viewed 253.4k times · Source

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.

Answer

Lieven Keersmaekers picture Lieven Keersmaekers · Jan 15, 2012

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

Related questions

SQL add Datetime add hour add Minute

In my table I have DateTime, Hour column. example : 2012-05-14 00:00:00.000 and 1230 How can I add this hour column into my Datetime column, so I can have 2012-05-14 12:30:00.000 I tried with this: SELECT DATE_DEBUT, HEURE_DEBUT, DATEADD(hour, …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More