Problem with IF NOT EXISTS query in ms sql

Question

I want to script query to validate if not have existing column test to add it after check condition. But in those two ways it is not working.

Way 1:

IF [dbo].[TEST].TEST NOT EXISTS
BEGIN
 ALTER TABLE [dbo].[TEST] ADD TEST INT;

Way 2:

ALTER TABLE [dbo].[TEST] ADD TEST INT
where NOT EXISTS in ([dbo].[TEST].TEST);

You want to check if the table exists or if any rows exist in the table? — Charlieface, Mar 23 '23 at 11:08
@Charlieface I want to check if the row exist and if it's not exist to add it in the table. — Ceci A., Mar 23 '23 at 11:12

score 5 · Accepted Answer · answered Mar 23 '23 at 11:12

5

If you want to script conditional addition of a column (only if it does not already exist in the table), you can use the sys.columns system catalogue in this way:

IF NOT EXISTS
(
  SELECT
    *
  FROM
    sys.columns
  WHERE
    object_id = OBJECT_ID('dbo.TEST')
    AND name = 'TEST'
)
  ALTER TABLE
    dbo.TEST
  ADD
    TEST int NULL
;

answered Mar 23 '23 at 11:12

Andriy M

22,983
6
59
103

1

OBJECT_ID has its own issues, eg https://dba.stackexchange.com/a/56398/220697, maybe join sys.tables – Charlieface Mar 23 '23 at 22:27

Problem with IF NOT EXISTS query in ms sql

1 Answers1