How to check whether a given column of table allow null value or not in sql server?

We can check whether a column of table allow null value or not using COLUMNPROPERTY

Syntax:

COLUMNPROPERTY(Table_Id,Column_Name,'AllowsNull')

Paramters:

Table_Id: Id of the table object. We can get ID of any table by using OBJECT_ID function. It is a integer number.

Column_Name: It is name of the column which we have to check is allow null value. It is VARCHAR(128)

Property_Name: It accespts predefined property name. Is it varchar data type. In this property will be AllowNull

Returns: It returns an integer value. If Column allows null value then it returns 1 and if not allows null value then it returns 0. If either table or column name doesn’t exist it returns NULL.  For example:

Suppose we have created a table by executing the following query:

CREATE TABLE Student(

    Roll_No BIGINT IDENTITY NOT NULL,

    Stu_Name VARCHAR(100) NULL,

    DOB DATETIME NOT NULL,

    IsActive BIT

)

We can check each column is allowing null value by following sql query:

SELECT

    COLUMNPROPERTY(OBJECT_ID('Student'),'Roll_No','AllowsNull'),

    COLUMNPROPERTY(OBJECT_ID('Student'),'Stu_Name','AllowsNull'),

    COLUMNPROPERTY(OBJECT_ID('Student'),'DOB','AllowsNull'),

    COLUMNPROPERTY(OBJECT_ID('Student'),'IsActive','AllowsNull')

   

Output:

0   1   0   1