Check if a column exists before adding the column to SQL table without altering table for each column

Question

I am trying to alter a table to add three new columns but I would like to check if the columns names before adding and if it already exists, just skip else add the column,

 ALTER TABLE TESTTABLE
 ADD [ABC] [int] ,
     [XYZ] [ [int] ,
     [PQR]  [int] 
GO

I have the below script

IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'ABC'
      AND Object_ID = Object_ID(N'TESTTABLE'))
BEGIN
    ALTER TABLE TESTTABLE
    ADD [ABC] int
END

but this has to be done for each column is there a better way to achieve this?

Are you looking to automate the creation of a table or just too lazy to look in SSMS / `sys.columns`? — Chris Pickford, Jul 15 '16 at 16:10
Possible duplicate of [How to check if a column exists in SQL Server table](http://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-sql-server-table) — Paul Abbott, Jul 15 '16 at 16:12
Doing this completely in t-sql means you will have to do this one column at a time. — Sean Lange, Jul 15 '16 at 16:13

score 5 · Answer 1 · answered Feb 16 '17 at 15:26

If you're sure these columns will always and only be added together at the same time, you can use IN to check for existence of any and then add them all if none exist:

IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name IN (N'ABC',N'XYZ',N'PQR')
      AND Object_ID = Object_ID(N'TESTTABLE'))
BEGIN
    ALTER TABLE TESTTABLE
    ADD [ABC] int,
        [XYZ] int,
        [PQR] int
END

Do note that this will not execute if any one of your columns already exists. If there's a chance of that happening, you'll need to make each check individually as you're already doing.

CPHPython · Answer 2 · 2018-04-15T15:33:47.553

2

Simple and dirty, you use directly the column names in the condition with COL_LENGTH (which returns NULL if the column does not exist, i.e. "on error"):

DECLARE @tb varchar(30) = 'TESTTABLE'
IF  COL_LENGTH(@tb, 'ABC') IS NULL
AND COL_LENGTH(@tb, 'XYZ') IS NULL
AND COL_LENGTH(@tb, 'PQR') IS NULL
BEGIN
    ALTER TABLE TESTTABLE
    ADD [ABC] int, [XYZ] int, [PQR] int
END

edited Apr 15 '18 at 15:33

answered Jul 15 '16 at 19:08

CPHPython

12,379
5
59
71

Check if a column exists before adding the column to SQL table without altering table for each column

2 Answers2