Name and description maximum length

Copy SQL

WITH length_checks AS (select 
n.nspname as table_schema,
c.relname as table_name,
a.attname as column_name,
regexp_replace(pg_get_constraintdef(o.oid),'CHECK ', '','g') AS check_clause,
'TABLE CHECK' AS check_type, 
o.conname AS constraint_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on o.connamespace=n.oid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
where o.contype = 'c' 
AND cardinality(o.conkey)=1
AND c.relkind IN ('r', 'f')
AND regexp_replace(pg_get_constraintdef(o.oid),'CHECK ', '','g') ~*'length[(]'
UNION ALL SELECT cdu.table_schema, cdu.table_name, cdu.column_name, cc.check_clause, 'DOMAIN CHECK' AS check_type, 
d.domain_schema||'.'||d.domain_name || '.' || cc.constraint_name AS name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) 
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name) 
INNER JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE', 'FOREIGN') AND cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.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)
AND cc.check_clause~*'length[(]'),
columns AS (SELECT A.table_schema, A.table_name , A.column_name, CASE WHEN data_type ILIKE 'character%' AND character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length::text || ')'
WHEN data_type ILIKE 'timestamp%' AND datetime_precision IS NOT NULL THEN data_type || '(' || datetime_precision || ')'
WHEN data_type ILIKE 'numeric%' AND numeric_precision IS NOT NULL THEN data_type || '(' || numeric_precision::text || ',' ||coalesce(numeric_scale,0)::text || ')'
WHEN data_type ILIKE 'interval%' AND interval_type IS NOT NULL THEN data_type || '(' || interval_type::text || ')'
WHEN data_type='USER-DEFINED' THEN udt_schema || '.' || udt_name 
ELSE data_type END AS data_type
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
WHERE T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
AND column_name~*'(nimi|nimetus|pealkiri|name|title|kirjeldus|kommentaar|selgitus|lugu|description|comment|explanation|story)')
SELECT column_name.table_schema, column_name.table_name,
column_name.column_name AS name_column, column_name.data_type AS name_column_type, lc_name.check_clause AS name_column_check, lc_name.check_type AS name_column_check_type,
column_description.column_name AS description_column, column_description.data_type AS name_column_type, lc_description.check_clause AS description_column_check, lc_description.check_type AS description_column_check_type
FROM columns AS column_name LEFT JOIN length_checks AS lc_name USING (table_schema, table_name, column_name),
columns AS column_description LEFT JOIN length_checks AS lc_description USING (table_schema, table_name, column_name)
WHERE column_name.table_schema=column_description.table_schema
AND column_name.table_name=column_description.table_name
AND column_name.column_name~*'(nimi|nimetus|pealkiri|name|title)'
AND column_description.column_name~*'(kirjeldus|kommentaar|selgitus|lugu|description|comment|explanation|story)'
ORDER BY column_name.table_schema, column_name.table_name;