Redshift: Generate SQL to Find the Max Length of a Table’s Text Columns

by

I always end up writing this again as I forget to save it!

It generates the SQL that you then need to execute. Advise caution on very large tables.

-- Generate SQL to find Max length of all of a table's text columns:
SELECT 
	'SELECT ' || 
	LISTAGG('MAX(LEN(' || column_name || ') AS max_' || column_name || ' as ' || column_name, ', ') WITHIN GROUP (ORDER BY ordinal_position) || 
	' FROM ' || table_schema || '.' || table_name
FROM 
	SVV_COLUMNS
WHERE 
	table_schema = 'mySchema' AND table_name= 'myTable'
	AND data_type IN ('character', 'character varying')
GROUP BY
	table_schema,
	table_name
;

Leave a Comment