How to generate SQL DDL Script from Redshift for Reverse Engineering ?

In order to CREATE TABLE/ SCHEMA DDL for already existing table or schema in Redshift you need to do the following as per AWS instructions:


STEP 1: Create view "v_generate_schema_ddl"


You can recreate the table/schema DDL by running scripts called v_generate_tbl_ddl.sql and v_generate_schema_ddl.sql. 


The scripts can be downloaded from amazon-redshift-utils, which is part of the Amazon Web Services - Labs git hub repository. Please use these scripts' github links mentioned in the reference section below.


Reference:

1) https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_schema_ddl.sql

2) https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql



STEP 2: Run Query and download results

Example

select * from admin.v_generate_tbl_ddl where schemaname = 'SchemaName'

STEP 3: Copy last column in separate text file



STEP 4: Replace all double quotes




STEP 5: Copy and paste in SqlDBM in "Reverse Engineering" tab

 


    Is this article helpful for you?