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 Checking if a column exists

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<br />
		 
		 --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