MS SQL Server - How to check and add a column if it doesn't exist?

How have an existing table and want to check if a column name already exists. If not, insert a new column to that table.

Try this query:

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'table_name' AND COLUMN_NAME = 'col_name')
BEGIN
  ALTER TABLE table_name
    ADD col_name data_type NULL
END;

Or this shorter query:

IF COL_LENGTH ('schema_name.table_name.col_name') IS NULL
BEGIN
  ALTER TABLE table_name
    ADD col_name data_type NULL
END;

For example, check if the column city exists in table realestate and add a new column if it’s missing:

IF NOT EXISTS (
  SELECT
    *
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'realestate' AND COLUMN_NAME = 'city')
BEGIN
  ALTER TABLE realestate
    ADD city TEXT NULL
END;

---

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple relational databases. It’s free anyway.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.