Find Columns That Allows NULL In SQL Server

Introduction

The columns in a table, that allows NULL in a database sometimes can lead to performance issues. The Nullable columns may be one of the reason to lead query to performance bad. By making columns NOT NULL can help the query to perform well. In this article we will discuss a simple way to find table columns that allows NULL.

Using INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.COLUMNS view results information about all the columns for all tables and views in a current database. This view has a column as IS_NULLABLE, which holds the information about the columns is NULLABLE or NOT. If NULLABLE, then results as YES and not nullable, then results as NO.

Lets look into our sample T-SQL Statement.

Example
SELECT
    TABLE_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName,
    COLUMN_NAME AS ColumnName,   
    DATA_TYPE AS DataType
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES'
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

The above T-SQL results as shown below and the columns are self explanatory.

Fig. 1 Columns that allows NULL

Conclusion

In this article, we discussed about finding the table columns in a database that allows NULL. I hope you all found this article much useful. We will discuss more concepts in our upcoming article and please share your feedbacks in the comment section.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: