Nullable columns in a database can sometimes lead to performance issues. That is definitely not to say that nullable columns will always cause performance issues, but if you do happen to have performance issues, identifying nullable columns can potentially provide some clues as to where the issue lies. Sometimes making a column NOT NULL can help improve performance.

By “nullable columns” I mean, columns that allow for NULL. If the column’s definition doesn’t include NOT NULL, then it allows for NULL values and it’s “nullable”.

Below is code that allows you to list all nullable columns in a database in SQL Server.

Example 1 – INFORMATION_SCHEMA.COLUMNS

This information schema view lists all columns that can be accessed by the current user in the current database. It has a column called IS_NULLABLE. If the relevant column allows for NULL, this column returns YES. Otherwise, NO is returned.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';

This lists all columns from the view.

Example 2 – INFORMATION_SCHEMA.COLUMNS with Fewer Columns Specified

You might not want all columns returned from the view. Here’s an example with fewer columns returned.

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_DEFAULT,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES'
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

Example 3 – Using sys.columns

If you don’t want to use the INFORMATION_SCHEMA.COLUMNS view, then you could query the sys.columns view instead.

However, you’ll need to do some joins if you want to return the tables and/or schema, etc.

Example:

SELECT 
    SCHEMA_NAME(t.schema_id) AS [Schema],
    t.name AS [Table],
    c.name AS [Column],
    dc.definition AS [Column Default],
    ty.name AS [Data Type]
FROM sys.tables AS t
INNER JOIN sys.columns AS c 
    ON t.object_id = c.object_id
LEFT JOIN sys.types AS ty 
    ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc  
    ON c.default_object_id = dc.object_id
WHERE c.is_nullable = 1
ORDER BY [Schema], [Table], [Column];