Find required (non nullable) columns in Azure SQL Database

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.