SQL SERVER: How to check column existence before adding new column in table?

Introduction: In this article I am going to explain how to check whether column exists in table or not; if not only then add the column.

In previous articles I have explained How to use merge in sql server to insert, update and delete in single statement and Convert table data to xml format using for xml path() and Get n random number of records from table and Case statement to sort records by different columns of different data types and Generate row number/serial number without ordering by any columns

Description: Very often we need to add new columns in tables. We write ALTER TABLE command in development server to add new column in table using the following syntax:

ALTER TABLE table_name ADD column_name datatype.

We create a script for adding this column on production server.

If somehow someone executes the ALTER TABLE script more than one time on production server than it will generate error message as"

Msg 2705, Level 16, State 4, Line 1           

Column names in each table must be unique. Column name 'column_name' in table 'table_name’ is specified more than once.

So it is always better to check whether column exists in table or not before adding a new column in table especially for production server.

Let’s write the script for finding whether column is present in table or not and only add the column if it does not exists.

Implementation: Let's create a demo table for demonstration purpose using the following script.

CREATE  TABLE dbo.tbEmployeeMaster

(

            EmployeeId     INT IDENTITY(1,1) PRIMARY KEY,

            Name              VARCHAR(100),

            Salary              DECIMAL(18,2)        

)

--Insert some dummy data into the table

GO

INSERT INTO dbo.tbEmployeeMaster

VALUES

('Aman',34000),

('Rohan',48000),

('Varun',80000),

('Arjun',37000),

('Raghav',22000),

('Sameer',12000);

--Check data in table

SELECT * FROM dbo.tbEmployeeMaster 

EmployeeId	Name	Salary
1	Aman	34000.00
2	Rohan	48000.00
3	Varun	80000.00
4	Arjun	37000.00
5	Raghav	22000.00
6	Sameer	12000.00

Now let’s write the scripts for finding whether column is present in table or not.

I have listed 4 methods to check for columns existence as below.  

Using INFORMATION_SCHEMA.COLUMNS

One of the methods to check for the column in a table is to use INFORMATION_SCHEMA system table for column system view.

Execute the following:

IF NOT EXISTS

(

            SELECT 1 FROM INFORMATION_SCHEMA.Columns

            WHERE

            TABLE_SCHEMA='dbo' AND

            TABLE_NAME = 'tbEmployeeMaster' AND

            COLUMN_NAME = 'Age'

)

BEGIN

            ALTER TABLE dbo.tbEmployeeMaster ADD Age INT

END

As you can see using INFORMATION_SCHEMA.COLUMNS we are checking whether the column we need to add already exists in table or not. If it does not exists, we are adding it. 

Using SYS.COLUMNS

We can also use the SYS.COLUMNS system table to check if column exists in a table or not.

Execute the following:

IF NOT EXISTS

(

   SELECT 1

    FROM SYS.COLUMNS

    WHERE OBJECT_ID = OBJECT_ID('dbo.tbEmployeeMaster')

    AND NAME = 'Age'

)

BEGIN

            ALTER TABLE dbo.tbEmployeeMaster ADD Age INT

END

As you can see using SYS.COLUMNS we are checking whether the column we need to add already exists in table or not. If it does not exists, we are adding it.  

Using COL_LENGTH

This function returns the length of the column if it exists in the table. If not, it will return NULL.

Execute the following:

IF COL_LENGTH('dbo.tbEmployeeMaster', 'Age') IS NULL

BEGIN

ALTER TABLE dbo.tbEmployeeMaster ADD Age INT

END

Here using COL_LENGTH we are checking the existence of the column and adding it if is not there in the table.

Using COLUMNPROPERTY

This function returns the value of the column’s property if it exists in the table. If not, it will return NULL.

Execute the following:

IF COLUMNPROPERTY(OBJECT_ID('dbo.tbEmployeeMaster'), 'Age', 'ColumnId') IS NULL

BEGIN

ALTER TABLE dbo.tbEmployeeMaster ADD Age INT

END

Here using COLUMNPROPERTY we are checking the existence of the column and adding it if is not there in the table.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post