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) )
The table Tab1 in the above script has a column name Spec_Table that has the name of many other tables, for example. I want only that tables which has the column Occasion. So here, I am passing the table name dynamically to the Object_ID() method.
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'
The function COL_LENGTH() takes two arguments. The first argument is the table name and the second is the column name that you want to check. You put it in an IF…ELSE condition.
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.