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.

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.