SQL Server – Getting Not Null Field Properties From a Table

The SQL query below is pretty simple and has helped me quite a bit. Maybe it could help you one day!
Particularly, I am using it to implement my validation classes from a existing database.

1
2
3
4
5
6
SELECT o.Name AS TABLE_NAME,
       c.Name AS Field_Name,
       c.is_nullable AS Allow_Null
FROM sys.columns c
     INNER JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.Name = '<table_name>' AND c.is_nullable = 0

All fields with "NOT NULL" properties will be displayed

To be more specific, the result from this query is really helpful to discover all mandatory fields and then implement my scope classes like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public static bool CreateUserScopeIsValid(this User user)
{
    return AssertionConcern.IsSatisfiedBy
    (
        AssertionConcern.AssertNotNullOrEmpty(user.Username, "Username should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.Email.Address, "E-mail should not empty"),
        AssertionConcern.AssertTrue(Email.IsValid(user.Email.Address), "Invalid e-mail"),
        AssertionConcern.AssertNotNullOrEmpty(user.Password, "Password should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.Name, "Name should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.Gender, "Gender should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.BirthDate, "Birth Date should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.SocialSecurityNumber, "Social Security Number should not empty"),
        AssertionConcern.AssertNotNullOrEmpty(user.Occupation, "Occupation should not empty")
    );
}

The class above will validate all required fields before insert or update statement as well as return a list of domain notifications based on my data integrity checklist.

References:
SQL Authority - Pinal Dave
Assertion Concern Pattern - Vernon Vaughn
Domain Notification Pattern - Martin Fowler

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.