Script for checking if a column exists in a given table and then add the column

Posted by Niladri.Biswas under Sql Server category on 4/4/2012 | Points: 40 | Views : 3903

Post Code |

Search Codes |

Code Home

In many situations we need to alter an existing table to add a new column to it. Here will will see three approaches for doing so

A) Querying sys.Columns

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'ColumnName' AND Object_ID = Object_ID(N'[dbo].[TableName]'))
BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] [varchar](100) NOT NULL
END

B) Using Col_Length

IF COL_LENGTH(N'[dbo].[TableName]', 'ColumnName') IS NULL

BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] [varchar](100) NOT NULL
END

Hope this helps

C) Querying INFORMATION_SCHEMA.columns

IF NOT EXISTS 
(
	SELECT * 
	FROM INFORMATION_SCHEMA.columns 
	WHERE table_name = 'TableName'
	AND   column_name = 'ColumnName'
)
BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] [varchar](100) NOT NULL
END

Alert Moderator

Bookmark It

Comments or Responses

Posted by: Pandians on: 4/3/2012 Level:Silver | Status: [Member] [MVP] | Points: 10

Hi

In your "A) Querying sys.Columns ". The condition should be "IF NOT EXISTS" (If the column already exists then How can you add the same column again?)
In your "B) Using Col_Length ". The condition should be "IS NULL"

Kindly make change In your both sections A) and B) as given above

Posted by: Pandians on: 4/5/2012 Level:Silver | Status: [Member] [MVP] | Points: 10

Thanks for accepted this changes!

Latest Code Snipptes