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

Something like this

select * from (describe table mytable)

asked 21 Sep, 10:50

Baron's gravatar image

Baron
1.4k95109130
accept rate: 50%

1

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.

(21 Sep, 11:36) Volker Barth
Replies hidden

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

(22 Sep, 02:53) Baron

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;

permanent link

answered 22 Sep, 02:48

Baron's gravatar image

Baron
1.4k95109130
accept rate: 50%

edited 22 Sep, 04:18

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.

(22 Sep, 03:09) Volker Barth
Replies hidden

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

(22 Sep, 03:48) Baron

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

(22 Sep, 04:16) Baron
Replies hidden

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.

(22 Sep, 04:35) Volker Barth

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

But where to add the order by exactly?

(22 Sep, 05:30) Baron

The full LIST syntax is

LIST( 
[ALL | DISTINCT ] string-expression
[, delimiter-string ]
[ ORDER BY order-by-expression [ ASC | DESC ], ... ] )
(22 Sep, 05:41) Volker Barth

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;

(22 Sep, 07:05) Baron

order by tname, cols

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

(22 Sep, 08:19) Volker Barth

So you mean only order by cols?

(22 Sep, 09:05) Baron
1

Yes, that's what I mean.

(22 Sep, 09:14) Volker Barth
1

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;

(24 Sep, 04:59) Baron
showing 3 of 11 show all flat view

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.

permanent link

answered 21 Sep, 11:22

Breck%20Carter's gravatar image

Breck Carter
32.0k5187091037
accept rate: 20%

edited 21 Sep, 11:23

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×20

question asked: 21 Sep, 10:50

question was seen: 160 times

last updated: 24 Sep, 04:59