How to know the whether a column already exists in table by running a query.

Problem: How to know the whether a column already exists in table by running a query.

Solution:  There are system objects in the object catalog views which can shows us if a column on the table.

1.       SYS.COLUMNS

2.       INFORMATION_SCHEMA.COLUMNS

3.       COLUMNPROPERTY

4.       COL_LENGTH

All these above objects can be helpful to do an if exists check and let’s see a few examples on how to use each of these objects.

Example:

For checking the columns lets create a test table and then we shall explore various ways to check the columns exists and properties of columns.

CREATE TABLE dbo.Test

(

Test_id INT,

Test_Name Varchar(40)

)

·         Sys.Objects and Sys.Columns comined gives us schema name, table name and column name.

 SELECT Schema_name(o.schema_ID) as SCHEMANAME,object_name(o.object_ID) AS TABLENAME,C.name AS COLUMNNAME

  FROM

  SYS.OBJECTS O

  INNER JOIN

  SYS.COLUMNS C

  ON O.Object_id=C.Object_id

  where c.name ='Test_id'

   and object_name(o.object_ID) ='Test'

   and Schema_name(o.schema_ID) ='dbo'

·         Information_schema.columns views gives us flexibility to go for all the information in one single spot.

SELECT Table_schema,Table_Name,Column_Name

FROM INFORMATION_SCHEMA.COLUMNS ISC

WHERE Table_Catalog = 'DB'

                AND ISC.Table_Schema = 'dbo'

                AND ISC.Table_Name = 'Test'

                AND ISC.Column_Name = 'Test_id'

                AND ISC.Data_type = 'INT'

-- Checking the Column length

SELECT COL_LENGTH('Test','Test_id')  as COLUMN_LEGNTH

-- COLUMN PROPERTY shows properties of the columns

SELECT COLUMNPROPERTY( OBJECT_ID('Test.Test'),'Test_id','PRECISION')AS 'COLUMN_PRECISION';