One of the common tasks that developers deal with when working on database-related projects is ensuring that certain columns exist in a table. This task is particularly relevant when you’re working on scripts to modify the structure of a table, such as adding new columns, and you want to avoid errors caused by attempting to add an existing column. Let us see a script that will help check if a Column Exists in a Table.
The INFORMATION_SCHEMA.COLUMNS view can be used to verify the existence of a column. The INFORMATION_SCHEMA views provide access to database metadata, including information about columns in all tables in the database.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') BEGIN -- Column Exists END
In this example, a SELECT query is constructed to find a row in INFORMATION_SCHEMA.COLUMNS that match the specified table and column name. If such a row exists, the column exists in the table.
Another approach is to use the sys.columns view, which also provides metadata about columns in tables.
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName' AND Object_ID = Object_ID(N'schemaName.tableName')) BEGIN -- Column Exists END
In this case, the Object_ID function is used to get the ID of the table, and this is compared with the object_id column in sys.columns.
You can also use the COL_LENGTH function, which returns the length of a specified column. If the column does not exist, COL_LENGTH returns NULL.
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL BEGIN -- Column Exists END
One advantage of this approach is that COL_LENGTH only returns data about committed changes, irrespective of the isolation level.
Each method has its advantages, and the best one to use depends on your specific circumstances. The INFORMATION_SCHEMA.COLUMNS and sys.columns views provide a wealth of information about columns, while the COL_LENGTH function can be faster since it uses cached database metadata.
Always remember that when working directly with database metadata, you must ensure your database user has the necessary permissions. Without these, you may not get accurate results from your column existence checks. If you have any questions, you can contact me on twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)