table description as result set

[Baron]

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

Something like this

select * from (describe table mytable)

[VolkerBarth]

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.

[Baron]

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

[Baron · Accepted solution]

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;

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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

[Baron · Accepted solution]

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)?

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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

But where to add the order by exactly?

[VolkerBarth · Accepted solution]

The full LIST syntax is

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

[Baron · Accepted solution]

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;

[VolkerBarth · Accepted solution]

order by tname, cols

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

[Baron · Accepted solution]

So you mean only order by cols?

[VolkerBarth · Accepted solution]

Yes, that's what I mean.

[Baron · Accepted solution]

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;

[Breck_Carter]

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.