Re: Adding SHOW CREATE TABLE

On Sat, May 13, 2023 at 1:03 AM Ron <ronljohnsonjr@gmail.com> wrote:

On 5/12/23 18:00, Kirk Wolak wrote:

[snip]

Where do we draw the lines?

At other tables.

Does Table DDL include all indexes?

Absolutely!

It should include constraints, clearly.  I would not think it should have triggers.

Definitely triggers.  And foreign keys.

Literally everything within the <<CREATE TABLE X(...);>>.  (ie, no ALTER .. OWNER TO...)

ALTER statements, too.  If CREATE TABLE ... LIKE ... { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } can do it, then so should SHOW CREATE TABLE.

--
Born in Arizona, moved to Babylonia.

I can see the ALTER statements now.  Which is why I asked.

I don't like the idea of including the trigger DDL, because that would never execute in a clean environment.

(I've never used a tool that tried to do that when I've wanted the DDL)

I can go either way on index creation.

Does this imply SQL SYNTAX like:

SHOW CREATE TABLE <table_name> 

  [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }] 

  [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] 

  [FOR {V11 | V12 | V13 | V14 | V15 }] ??

?

The goal for me  is to open the discussion, and then CONSTRAIN the focus.

Personally, the simple syntax:
SHOW CREATE TABLE table1;

Should give me a create table command with the table attributes and the column attributes, FKs, PKs, Defaults.  Etc.
But I would not expect it to generate index commands, etc.