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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

IF COL LENGTH  table name    column name   IS NULL BEGIN     ALTER TABLE table name     ADD  column name  INT END

[sql-server] Add a column to a table, if it does not already exist

Examples related to sql-server

Examples related to add

Examples related to not-exists