[Redshift] How to find table ID associated with DDL statement


How can I find the table id modified by a DDL statement in Redshift? STL_DDLTEXT doesn't have a column associated with table id. Any help would be appreciated.

asked 2 months ago18 views
1 Answer

from https://medium.com/@alex_handley/aws-redshift-handy-query-to-get-all-table-create-statements-for-a-schema-fcc0eaac03c1 something like the following should provide that information. Note that user generated schema's are not in pg_table_def search path by default

SET SEARCH_PATH TO <something>;

SELECT ddl FROM admin.v_generate_tbl_ddl JOIN pg_table_def ON ( admin.v_generate_tbl_ddl.schemaname = pg_table_def.schemaname AND admin.v_generate_tbl_ddl.tablename = pg_table_def.tablename ) WHERE admin.v_generate_tbl_ddl.schemaname = '<something>' GROUP BY admin.v_generate_tbl_ddl.tablename, ddl, "seq" ORDER BY admin.v_generate_tbl_ddl.tablename ASC, "seq" ASC;

from https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql will provide you with detailed table information including distkey, sortkey, constraints, not null, defaults, etc.

answered 2 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions