SQL SERVER – Check If a Column Exists 2016 Onwards

It is totally fine to use the syntax and code which is working fine in our code, particularly if it is supported by your application and server. However, sometimes it is fun to learn new things as well. Let us learn how you can check if a column exists in a table from SQL Server 2016 and onwards.

If you are using any version of SQL Server 2005 and onwards, you can use the following syntax.

IF COL_LENGTH('SchemaName.TableName', 'ColumnName') IS NOT NULL
BEGIN
    -- Column Exists Operations
ELSE
    -- Column NOT Exists Operations
END

If you are using any version of SQL Server 2016 and onwards, you can use the following syntax.

ALTER TABLE SchemaName.TableName DROP COLUMN IF EXISTS ColumnName

The same syntax words with views, procedures, constraints and many other SQL Server objects.

With that said use the one which makes use of it, most comfortable to check if a column exists.

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.

• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)