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. We will create a user defined function to check column existence.
In previous articles I have explained How to split or convert delimited string into rows without using function and Sort alphanumeric string data in table and CTE recursive query to get parent child hierarchy with levels and Add unique constraint/index on a column that allows multiple null values and Case statement to sort records by different columns of different data types
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:
Now let’s write the scripts for finding whether column is present in table or not.
One of the methods to check for the column in a table is to use INFORMATION_SCHEMA system table for column system view.
In previous articles I have explained How to split or convert delimited string into rows without using function and Sort alphanumeric string data in table and CTE recursive query to get parent child hierarchy with levels and Add unique constraint/index on a column that allows multiple null values and Case statement to sort records by different columns of different data types
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.
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.
But every time we need to write the above query to check whether column exists in table or not? So it will be better to create a function for this. We can create a user defined function to check for column existence. Below is the function.
CREATE FUNCTION ColumnExists
There are some other ways to check for column
existence. You can read about that in my article How to check whether column exists in table or not. In that article I have mentioned four methods
to check for column existence.
But every time we need to write the above query to check whether column exists in table or not? So it will be better to create a function for this. We can create a user defined function to check for column existence. Below is the function.
User Defined Function:
(
@SchemaName VARCHAR(20),
@TableName VARCHAR(100),
@ColumnName VARCHAR(100)
)
RETURNS BIT AS
BEGIN
DECLARE
@Exists BIT=0;
IF EXISTS
(
SELECT
1 FROM INFORMATION_SCHEMA.Columns
WHERE
TABLE_SCHEMA=@SchemaName AND TABLE_NAME =
@TableName AND COLUMN_NAME = @ColumnName
)
BEGIN
SET
@Exists = 1
END
RETURN
@Exists;
END
To check we need to call this function like below:
GO
IF
dbo.ColumnExists('dbo', 'tbEmployeeMaster', 'Age') = 0
BEGIN
ALTER TABLE DBO.tbEmployeeMaster Age INT
END
Explanation: As we can see we just need to pass schema name, table name and column name to our user defined function and it will return 1 if column already exists in table otherwise it will return 0. So we are adding new column when this function returns 0.
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.
If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..