Find required (non nullable) columns in Snowflake

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

Query

select col.table_schema,
       col.table_name,
       col.column_name,
       col.data_type,
       case when col.numeric_precision is not null
            then col.numeric_precision
         else col.character_maximum_length
         end as max_length,
         col.numeric_scale as precision
from information_schema.columns col
where col.is_nullable = 'NO'
      and col.table_schema != 'INFORMATION_SCHEMA'
order by col.table_schema,
         col.table_name,
         col.column_name;

Columns

  • table_schema - name of the table schema
  • table_name - name of the table
  • column_name - name of the column
  • data_type - column data type
  • max_length - maximum length of the data type
  • precision - For number type the precision indicates the number of significant digits to the right of the decimal point. For other it is null.

Rows

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

Sample results

Sample results of non-nullable columns in the SNOWFLAKE_SAMPLE_DATA database.

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