How to check if column exists in SQL Server table

Check if column exists on SQL 2005

How to check if column exists in SQL Server table

SQL -> Check Column exists in table, if not, add

Here is a brief example on how we can check if a column exists in a table. But, do we have to check this? Imagine, you wrote a t-sql to add a column into a table and someone executes it more than once. This simple check will prevent the SQL from throwing error message like Column names in each table must be unique...

	--Create test table
IF OBJECT_ID('MyTempTable1','U') IS NOT NULL
      DROP TABLE MyTempTable1

CREATE TABLE MyTempTable1 (
            column1 varchar(2) null,
            column2 varchar(2) null,
            column3 varchar(2)
      )
      
--check point
SELECT * FROM MyTempTable1
 
--Check if column4 exists before adding
IF col_length('MyTempTable1','column4') is null
  BEGIN
      ALTER TABLE MyTempTable1
      ADD column4 [float] NULL
  END
 
--check point
SELECT * FROM MyTempTable1
 
--Check if column5 exists before adding
IF NOT EXISTS (SELECT 'b' FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = 'MyTempTable1' AND COLUMN_NAME = 'column5')
  BEGIN
      ALTER TABLE MyTempTable1
      ADD column5 [float] NULL
  END
 
--check point
SELECT * FROM MyTempTable1

How to check if a column exists in a temp table?

IF col_length('tempdb..#MyTempTableName','column4') is null
  BEGIN
      ALTER TABLE #MyTempTableName'
      ADD column4 [float] NULL
  END