Decrease Font Size
Increase Font Size
   BLOG

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

Comments (3) -

  • Stop Dreaming Start Action

    7/28/2009 8:34:49 AM |

    can used for musql database?

  • bryian

    7/29/2009 2:50:36 PM |

    hello,
    Yes, we can do the same for MySql database. The above syntax is for SQL 2005.

    Thanks,
    Bryian Tan

  • sdf

    1/4/2010 9:45:32 PM |

    hi

Pingbacks and trackbacks (1)+

Add comment

Loading