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);

Answer 1

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
;