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