SQL SERVER – Checking If a Column Exists in a Table

SQL SERVER - Checking If a Column Exists in a Table columnexists-800x457 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.

WHERE TABLE_NAME = 'myTableName'
AND COLUMN_NAME = 'myColumnName')
-- Column Exists

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.

Using sys.columns

Another approach is to use the sys.columns view, which also provides metadata about columns in tables.

WHERE Name = N'columnName'
AND Object_ID = Object_ID(N'schemaName.tableName'))
-- Column Exists

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
-- Column Exists

One advantage of this approach is that COL_LENGTH only returns data about committed changes, irrespective of the isolation level.

Wrapping Up

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)

Information Schema, SQL Column, SQL Table Operation, System Object
Previous Post
Navigating SQL Server CPU and Memory Usage Woes
Next Post
SQL SERVER – Techniques for Retrieving Random Rows

Related Posts

Leave a Reply