Jul 15, 2013

How to check whether a given column exists in given table in sql server?

In sql server information about all the columns and its properties of a table is stored in the system view sys.Columns.

Suppose we have table named Student. We want to check int this table, column name Roll_No is present or not. Sql qurey for this:

IF EXISTS(SELECT 1 FROM Sys.columns WHERE object_id = OBJECT_ID('Student') AND name = 'Roll_No')
    SELECT 'Roll_No column in the Student table is present.'
ELSE
    SELECT 'Roll_No column in the Student table is not present.'

1 comment:

  1. if COLUMNPROPERTY(N'Student','Roll_No','ColumnId') is not null
    SELECT 'Roll_No column in the Student table is present.'
    ELSE
    SELECT 'Roll_No column in the Student table is not present.'

    ReplyDelete