Question Re: table description as result set in Technology Q&A

table description as result set

Baron — Tue, 21 Sep 2021 10:50:40 GMT

Is it possible to have the description of a table as a result set?

Something like this

select * from (describe table mytable)

Re: table description as result set

Breck_Carter — Tue, 21 Sep 2021 11:22:10 GMT

The DESCRIBE statement cannot be coded inside a server-side SQL operation because it a client-side ISQL statement.

However, you can SELECT FROM SYS.SYSCOLUMNS to get the same result:

DESCRIBE TABLE rroad_sample_memo;
SELECT LEFT ( cname, 32 ) AS "Column",
       LEFT ( coltype, 32 ) AS "Type",
       IF nulls = 'N' THEN 0 ELSE 1 ENDIF AS "Nullable",
       IF in_primary_key = 'N' THEN 0 ELSE 1 ENDIF AS "Primary Key"
  FROM SYS.SYSCOLUMNS
 WHERE tname = 'rroad_sample_memo'
 ORDER BY colno;

Column                           Type                             Nullable Primary Key 
-------------------------------- -------------------------------- -------- ----------- 
sampling_id                      unsigned int                            0           0 
sample_set_number                unsigned bigint                         0           1 
memo                             long varchar                            0           0 


Column                           Type                             Nullable Primary Key 
-------------------------------- -------------------------------- -------- ----------- 
sampling_id                      unsigned int                            0           0 
sample_set_number                unsigned bigint                         0           1 
memo                             long varchar                            0           0 
(3 rows)
Execution time: 0.002 seconds

FYI the system catalog views contain vastly more information than any DESCRIBE statement.

Re: table description as result set

VolkerBarth — Tue, 21 Sep 2021 11:36:14 GMT

FWIW, there's also the sa_get_table_definition system function that returns basically the CREATE TABLE statement etc. as a long varchar for the according table, but of course that is not the result set you are looking for.

Re: table description as result set

Baron — Wed, 22 Sep 2021 02:48:39 GMT

Thank you!!

The goal was to generate an insert statements for big tables to be used in a python script, at the end Syscolumns helped me a lot.

create or replace table table1(id1 int, name1 varchar(10));

create or replace table table2(id2 int, name2 varchar(10), surname2 varchar(10));

....

create or replace table table100(id100 int, name100 varchar(10), surname100 varchar(10), depart100 varchar(10));

select 'insert into ' || tname || '(' || list(cols) || ') values (' || list (vals) || ')' from (select tname, cname cols, (if coltype = 'varchar' then '''%S''' else '%S' endif) as vals from sys.syscolumns where tname in ('table1', 'table2', 'table100')) T1 group by tname;

Re: table description as result set

Baron — Wed, 22 Sep 2021 02:53:08 GMT

Yes, but it returns the result in a one single column/row, which doesn't help in my case

Re: table description as result set

VolkerBarth — Wed, 22 Sep 2021 03:09:20 GMT

And again LIST() is such a helpful aggregate function - you might make sure that both lists are sorted identically by adding an ORDER BY expression.

Re: table description as result set

Baron — Wed, 22 Sep 2021 03:48:32 GMT

Is the GROUP BY TNAME not enough to guarantee that the column names are correctly assigned to each TableName?

Re: table description as result set

Baron — Wed, 22 Sep 2021 04:17:00 GMT

Oh, you mean in order to keep the same order between vals and cols.

The question is here how does the list function work? Doesn't it take the same order as they appear in the subquery (of table T1)?

Re: table description as result set

VolkerBarth — Wed, 22 Sep 2021 04:35:26 GMT

In my understanding, unless you specify an ORDER BY clause, the SQL query engine is free to order result sets - and for LIKE, to order the different entries - to its preference according to the used access plans, i.e. it does not need to order them in any meaningful way.

And I'm quite sure you want column names and values in the "correct order" here.

Re: table description as result set

Baron — Wed, 22 Sep 2021 05:30:56 GMT

Yes, I need to have them in the "correct order" 🙂

But where to add the order by exactly?

Re: table description as result set

VolkerBarth — Wed, 22 Sep 2021 05:41:17 GMT

The full LIST syntax is

LIST( 
[ALL | DISTINCT ] string-expression
[, delimiter-string ]
[ ORDER BY order-by-expression [ ASC | DESC ], ... ] )

Re: table description as result set

Baron — Wed, 22 Sep 2021 07:05:52 GMT

Thanks, should it then look like this?

select 'insert into ' || tname || '(' || list(cols order by tname, cols) || ') values (' || list (vals order by tname, cols) || ')' from (select tname, cname cols, (if coltype = 'varchar' then '''%S''' else '%S' endif) as vals from sys.syscolumns where tname in ('table1', 'table2', 'table100')) T1 group by tname;

Re: table description as result set

VolkerBarth — Wed, 22 Sep 2021 08:19:40 GMT

order by tname, cols

tname is superfluous here, as you already group by tname.

Re: table description as result set

Baron — Wed, 22 Sep 2021 09:05:52 GMT

So you mean only order by cols?

Re: table description as result set

VolkerBarth — Wed, 22 Sep 2021 09:14:20 GMT

Yes, that's what I mean.

Re: table description as result set

Baron — Fri, 24 Sep 2021 04:59:03 GMT

adding order by cols helps to keep both lists in the same order, but both come in a false order (alphabeitcally instead of the real order).

So, the correct statement should look like this:

select 'insert into ' || tname || '(' || list(cols order by colno) || ') values (' || list (vals order by colno) || ')' from (select colno, tname, cname cols, (if coltype = 'varchar' then '''%s''' else '%s' endif) as vals from sys.syscolumns where tname in ('table1', 'table2', 'table100')) T1 group by tname;