Generating Redshift ddl (create statements) for tables that already exist

Sometimes (e.g. when transferring data between two Redshift clusters) you need to create a table that exists in one cluster but not another. In this case you need to know the ddl for the table, to create it.

If you don’t have it, the clever folks at AWS have some SQL for generating this.

First, you execute the SQL here to create a new view: admin.v_generate_tbl_ddl. (Note you need an admin schema to exist prior to running the SQL.)

You can then generate the ddl for any table simply by executing the following:

select ddl
from admin.v_generate_tbl_ddl
where schemaname = '{{ table schema name }}'
and tablename = '{{ table name }}'

There’s a tonne of other goodies on https://github.com/awslabs/amazon-redshift-utils. Well worth checking out…

2 Likes