Save output of show table extended to table?

[dplaut]

I want to save the output of 

    show table extended in catalogName like 'mysearchtext*';

to a table.

How do I do that?

[Kaniz_Fatma]

Hi @dplaut, To save the output of the SHOW TABLE EXTENDED command to a table, you can follow these steps:

First, execute the SHOW TABLE EXTENDED command with the desired regular expression pattern. This command provides detailed information about tables matching the given pattern, including basic table information, file system details, and more.

For example:

1. SHOW TABLE EXTENDED LIKE ’'mysearchtext*';

Examine the output to ensure it contains the relevant details you want to save.

Create a new table where you'll store this information. You can use the CREATE TABLE statement to define the schema of your new table. Adjust the column names and data types as needed.

Insert the output of the SHOW TABLE EXTENDED command into your newly created table. You can do this using an INSERT INTO statement. Make sure to specify the appropriate column names and values from the output.

[dplaut]

I can't get this to work. I get the error:
[PARSE_SYNTAX_ERROR] Syntax error at or near 'SHOW'.line 2, pos 0

insert into my_target_table (database, tablename, istemporary, information)
SHOW TABLE EXTENDED LIKE 'mytable';

[Kaniz_Fatma]

Hi @dplaut, Try this. You have missed a semicolon in your first statement.

insert into my_target_table (database, tablename, istemporary, information);

SHOW TABLE EXTENDED LIKE 'mytable';

[dplaut]

I would not have expected that to work as it violates rules I know about SQL and the purpose of the semicolon. And it does not work.

[PARSE_SYNTAX_ERROR] Syntax error at or near end of input.line 1, pos 75