Feature: Enhance show views

[TCeason]

Summary

After #14889 finished. We can enhance show views.

Display like this:

https://docs.snowflake.com/en/sql-reference/sql/show-views

And we need the most important display field is text.

text | The text of the command that created the view (e.g. CREATE VIEW …).

[blackstar-baba]

LGTM

[TCeason]

LGTM

You mean you want to finish this issue? If so I can assign to you 😀

[blackstar-baba]

yes，assign to me

[blackstar-baba]

I need some suggestions. I know how to get view information except for the SQL from system.tables, and I also know how to get the sql by view name, but I don't know how to join them together

[blackstar-baba]

Can i extend text column in system.tables?

[blackstar-baba]

@TCeason

[TCeason]

Can i extend text column in system.tables?

Yes. I think it's ok. And the col can be named as 'view_query'.

And maybe we also can create a new system table named 'views'? @sundy-li

[blackstar-baba]

SHOW FULL views IN test

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│   tables  │ table_type │ database │ catalog │       owner      │ engine │ cluster_by │         create_time        │     num_rows     │     data_size    │ data_compressed_size │    index_size    │        view_query       │
│   String  │   String   │  String  │  String │ Nullable(String) │ String │   String   │          Timestamp         │ Nullable(UInt64) │ Nullable(UInt64) │   Nullable(UInt64)   │ Nullable(UInt64) │          String         │
├───────────┼────────────┼──────────┼─────────┼──────────────────┼────────┼────────────┼────────────────────────────┼──────────────────┼──────────────────┼──────────────────────┼──────────────────┼─────────────────────────┤
│ user_view │ BASE TABLE │ test     │ default │ NULL             │ VIEW   │            │ 2024-03-22 13:58:17.709271 │             NULL │             NULL │                 NULL │             NULL │ SELECT * FROM test.user │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row read in 8.005 sec. Processed 2 row, 495 B (0.25 row/s, 61 B/s)

[TCeason]

SHOW FULL views IN test



┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

│   tables  │ table_type │ database │ catalog │       owner      │ engine │ cluster_by │         create_time        │     num_rows     │     data_size    │ data_compressed_size │    index_size    │        view_query       │

│   String  │   String   │  String  │  String │ Nullable(String) │ String │   String   │          Timestamp         │ Nullable(UInt64) │ Nullable(UInt64) │   Nullable(UInt64)   │ Nullable(UInt64) │          String         │

├───────────┼────────────┼──────────┼─────────┼──────────────────┼────────┼────────────┼────────────────────────────┼──────────────────┼──────────────────┼──────────────────────┼──────────────────┼─────────────────────────┤

│ user_view │ BASE TABLE │ test     │ default │ NULL             │ VIEW   │            │ 2024-03-22 13:58:17.709271 │             NULL │             NULL │                 NULL │             NULL │ SELECT * FROM test.user │

└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row read in 8.005 sec. Processed 2 row, 495 B (0.25 row/s, 61 B/s)

I think it's OK