Microsoft – SQLServer – Determine if Column Exists (in Table)

Microsoft – SQLServer – Determine if Column Exists (in Table)

One way of determining whether a Column is already present in a Table is
to Query against the ColumnProperty Function:

Syntax:

     -- Get ColumnProperty
     -- If ColumnID return is null, then Column does not exist
     -- Else Column Exists 
     select 
              columnProperty
                          (
                                object_id('objectName')
                             ,  <columnName>
                             ,  'columnID'
                          ) as columnID

Sample:

     -- Get ColumnProperty
     -- If ColumnID return is null, then Column does not exist
     -- Else Column Exists 
     select 
              columnProperty
                          (
                                object_id('dbo.company')
                             ,  localityID
                             ,  'columnID'
                          ) as columnID

There are other samples over the .Net.

As always Stackoverflow.com has good coverage.

The http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table URL presents good insight as to how one can determine this information by:

a) Query INFORMATION_SCHEMA.Columns, sys.columns (syscolumns)

b) Query Col_Length function

References:

1) ColumnProperty
 http://msdn.microsoft.com/en-us/library/ms174968.aspx
2) How to Check if a Column Exists in SQL Server

http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s