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