More on AWS RedShift

(We marked visited pages blue)

Script to extract the DDL in AWS RedShift

If you wanna find the table details/description you can use the below script and output the full metadata for a table. Just replace INSERT_TABLENAME_HERE and INSERT_SCHEMA_NAME_HERE with the scheme and table name.

SELECT DISTINCT n.nspname AS schemaname
 ,c.relname AS tablename
 ,a.attname AS COLUMN
 ,a.attnum AS column_position
 ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE
 ,pg_catalog.format_encoding(a.attencodingtype) AS encoding
  ,a.attisdistkey AS distkey
 ,a.attsortkeyord AS sortkey
 ,a.attnotnull AS notnull
 ,a.attencodingtype AS compression
 ,con.conkey AS primary_key_column_ids
 ,con.contype AS con_type
FROM pg_catalog.pg_namespace n
 ,pg_catalog.pg_class c
 ,pg_catalog.pg_attribute a
 ,pg_constraint con
 ,pg_catalog.pg_stats stats
WHERE n.oid = c.relnamespace
 AND c.oid = a.attrelid
 AND a.attnum  0
 AND c.relname NOT LIKE '%pkey'
 AND lower(c.relname) = 'INSERT_TABLENAME_HERE'
 AND n.nspname = 'INSERT_SCHEME_NAME_HERE'
 AND c.oid = con.conrelid(+)
ORDER BY A.ATTNUM
;


Leave a comment or a question in the comment area bellow if you fell like


Be a sport and

Search

Loading... Please wait

Subscribe to our Newsletter

Be a sport and

x