Summary of default values in Snowflake

Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.

Query

select column_default,
       count(distinct c.table_schema || '.' || c.table_name) as tables,
       count(*) as columns
from information_schema.columns c
left join information_schema.tables t
          on c.table_schema = t.table_schema
          and c.table_name = t.table_name
where t.table_type = 'BASE TABLE'
group by column_default
order by columns desc;

Columns

  • column_defualt - definition of default constraint (formula) and NULL for columns without a default value
  • tables - number of tables with this constraint (or number of tables with columns with no constraints for NULL row)
  • columns - number of columns with this particular constraint (or no constraint for NULL row)

Rows

  • One row represents one unique definition of default value
  • Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
  • Ordered by the number of occurrences from the most popular

Sample results

Different default column definitions:

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