CODEWITHSUNDEEP
sql-server
DoIt
DoIt

Reputation: 3428

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

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?

Upvotes: 7

Views: 31718

Answers (2)

CPHPython
CPHPython

Reputation: 13709

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

Upvotes: 2

Kenbo
Kenbo

Reputation: 141

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.

Upvotes: 5

Related Questions