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
– Chris Pickford, Commented Jul 15, 2016 at 16:10
Possible duplicate of How to check if a column exists in SQL Server table — Paul Abbott
– Paul Abbott, Commented Jul 15, 2016 at 16:12
Doing this completely in t-sql means you will have to do this one column at a time. — Sean Lange
– Sean Lange, Commented Jul 15, 2016 at 16:13

Kenbo · Accepted Answer · 2017-02-16 15:26:05Z

5

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.

answered Feb 16, 2017 at 15:26

Kenbo

1412 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CPHPython · Accepted Answer · 2018-04-15 15:33:47Z

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, 2018 at 15:33

answered Jul 15, 2016 at 19:08

CPHPython

13.9k5 gold badges68 silver badges77 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related