The query below lists all non-nullable columns in a database.

Query

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name,
    col.column_id,
    col.name as column_name,
    t.name as data_type,
    col.max_length,
    col.precision
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
where col.is_nullable = 0
order by schema_name,
    table_name,
    column_name;

Columns

• schema_name - name of the schema
• table_name - name of the table
• column_id - column position in a table
• column_name - name of the column
• data_type - column data type
• max_length - data type max length
• precision - data type precision

Rows

• One row: represents one table column
• Scope of rows: all non-nullable columns in all tables in a database
• Ordered by: schema, table name, column id

Sample results

Non-nullable columns in the AdventureWorksLT database.