Find required (non nullable) columns in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    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.

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept