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

Niladri.Biswas
Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 2837
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

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!

Login to post response