Mandatory columns for holding large textual values (comments, descriptions, etc.)

Copy SQL

WITH domains AS (SELECT
(SELECT nspname FROM pg_namespace WHERE pg_namespace.oid=pg_type.typnamespace) AS domain_schema,
typname AS domain_name,
typdefault AS domain_default,
CASE WHEN typnotnull=TRUE THEN 'NO' ELSE 'YES' END AS is_nullable
FROM pg_type
WHERE typtype='d')
SELECT c.table_schema, c.table_name, c.column_name, c.data_type, c.character_maximum_length, 
coalesce(c.column_default, d.domain_default) AS column_default
FROM INFORMATION_SCHEMA.columns AS c LEFT JOIN domains AS d USING (domain_schema, domain_name)
WHERE column_name~*'(komment|kirjeldus|comment|descr|selgitus)' AND column_name!~*'_(id|nr|kood|code|aeg|time|kp|date)$'
AND ((c.data_type='text') OR  (c.data_type='character varying' AND c.character_maximum_length IS NULL) OR (c.data_type='character varying' AND c.character_maximum_length>=1000)) 
AND (d.is_nullable='NO' OR c.is_nullable='NO') 
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') 
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY table_schema, table_name, ordinal_position;