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.

SQL SERVER - Check If a Column Exists 2016 Onwards columnexists-800x600

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.

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

, ,
Previous Post
SQL SERVER – Find Total Sessions by Database
Next Post
SQL SERVER – Deny Drop Permission for a Table

Related Posts

2 Comments. Leave new

  • Good one.

    Version of SQL Server 2016 and onwards can be used for adding a column?

    Reply
  • The 2016 code doesn’t just check for a column, it also drops it. Don’t run this if you want to keep your column!

    Reply

Leave a Reply

Menu