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

0

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.

已提问 1 年前291 查看次数
1 回答
0

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.

profile picture
已回答 1 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则