Nov 10, 2013

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

No comments:

Post a Comment