T-SQL Tutorial

SQL Check if column exists


Check if column exists in a table

Before adding a new column to a table or before deleting or renaming a column from a table, you should check if there is a column with the same name in the table.
To check if column exists in a table you need to use a Select statement on the information schema COLUMNS or you can use the function COL_LENGTH(table, column).

Example

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Certifications'
AND COLUMN_NAME='DESCRIPTION';

Result:

TABLE_NAMECOLUMN_NAMEDATA_TYPEIS_NULLABLE
CertificationsDESCRIPTIONvarcharYES


Check if column exists in a table

IF (EXISTS (SELECT *
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'Certifications'
   AND COLUMN_NAME='DESCRIPTION'))
   BEGIN
      PRINT 'Column Exists'
   END;
ELSE
   BEGIN
      PRINT 'No Column in a Table'
   END;

Result:

Column Exists


Using COL_LENGTH function

IF COL_LENGTH('dbo.Certifications', 'DESCRIPTION') IS NOT NULL
   BEGIN
      PRINT 'Column Exists'
   END;
ELSE
   BEGIN
      PRINT 'No Column in a Table'
   END;

Result:

Column Exists