1) Using sys.columns
In the first method, I am using the sys.columns object. It’s an in-build object in SQL Server, which returns a row for each column of an object (such as a Table or a View) that has columns.
I have an EmployeeDetails table, in which I want to check if column Area exists or not. The script will be,
SELECT 1 FROM sys.columns WHERE Name = N'Area' AND Object_ID = Object_ID(N'dbo.EmployeeDetails')
The output of the above script will return 1 if the column 'Area' exists in the table. Else, it will return nothing. You can use some other value (a string value) instead of 1 in the SELECT query.
I can use the above method in a more dynamic situation, such as, providing the table name dynamically to the Object_ID() method. For example,
SELECT T1.Spec_Table FROM Tab1 T1 WHERE EXISTS( SELECT 1 FROM sys.columns WHERE Name = N'Occasion' AND Object_ID = Object_ID(N'dbo.' + T1.Spec_Table) )
Using COL_LENGTH() Function
In the 2nd method, I am using a built-in function called COL_LENGTH() to check if a particular column exists in the table. The method returns a defined length of a column, in bytes.
Again, in this example I am using the EmployeeDetails table.
IF COL_LENGTH('EmployeeDetails', 'PresentAddress') IS NOT NULL PRINT 'Column Exists' ELSE PRINT 'Column doesn''t Exists'
You can use above function with a SELECT query to check the length of a particular column in a table.
SELECT COL_LENGTH('EmployeeDetails', 'Area')
If the column Area exists in the table, the result will be a number (a figure) or else it will return a NULL.
Well, that’s it. Using the above methods, now you can avoid adding duplicate column names. Both are useful in different scenarios.