Did you know?

Did you know that SQL Anywhere has a DESCRIBE TABLE statement?

CREATE TABLE t (
   pkey   INTEGER           NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data1  VARCHAR ( 100 )   NOT NULL DEFAULT '',
   data2  DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,
   data3  LONG BINARY       NOT NULL DEFAULT '' );

DESCRIBE TABLE t;

Column  Type          Nullable  Primary Key
pkey    integer          0           1
data1   varchar(100)     0           0
data2   decimal(11,2)    0           0
data3   long binary      0           0

Did you know that it's been there since Version 10?

How about this?

Did you know that SQL Anywhere has an sa_describe_query() procedure that returns all sorts of information about each and every column in a SELECT?

Did you know that sa_describe_query() has also been there since Version 10?

Did you also know that sa_describe_query() can do everything DESCRIBE TABLE can do, plus a lot more?

SELECT * FROM sa_describe_query ( 'SELECT * FROM t', 1 );

column_number name  domain_id domain_name domain_name_with_size   width   scale declared_width user_type_id user_type_name correlation_name base_table_id base_column_id base_owner_name base_table_name base_column_name nulls_allowed is_autoincrement is_key_column is_added_key_column
      1       pkey      2     int         int                         4     0            4       (NULL)         (NULL)            t            723            1            DBA                t                pkey          false            true           true             false
      2       data1     9     varchar     varchar(100)              100     0          100       (NULL)         (NULL)            t            723            2            DBA                t                data1         false            false          false            false
      3       data2    27     decimal     decimal(11,2)              11     2           11       (NULL)         (NULL)            t            723            3            DBA                t                data2         false            false          false            false
      4       data3    12     long binary long binary        2147483647     0        32767       (NULL)         (NULL)            t            723            4            DBA                t                data3         false            false          false            false

Here's a big difference that might not be obvious: DESCRIBE TABLE is an ISQL statement so you'll see "Syntax error near 'describe'" if you try to use it inside a stored procedure, whereas sa_describe_query works just fine in both contexts (ISQL and SQL):

CREATE PROCEDURE p ( IN @query LONG VARCHAR )
BEGIN
   SELECT name                  AS column_name,
          domain_name_with_size AS data_type
     FROM sa_describe_query ( @query, 1 )
    ORDER BY column_number;
END;

SELECT * FROM p ( 'SELECT * FROM t' );

column_name  data_type
pkey         int
data1        varchar(100)
data2        decimal(11,2)
data3        long binary

If the query involves a view, sa_describe_query() tells you where the columns are coming from (if it can):

CREATE VIEW v AS
SELECT data1,
       data2 * 10,
       pkey
  FROM t;

SELECT name                                              AS view_column_name,
       domain_name_with_size                             AS data_type,
       STRING ( base_table_name, '.', base_column_name ) AS base_column 
  FROM sa_describe_query ( 'SELECT * FROM v' )
 ORDER BY column_number;

view_column_name  data_type      base_column
data1             varchar(100)   t.data1
expression        numeric(13,2)  .
pkey              int            t.pkey

SELECT name                                              AS view_column_name,
       domain_name_with_size                             AS data_type,
       STRING ( base_table_name, '.', base_column_name ) AS base_column 
  FROM sa_describe_query ( 'SELECT * FROM SYSTAB' )
 ORDER BY column_number;

view_column_name    data_type        base_column
table_id            unsigned int     ISYSTAB.table_id
dbspace_id          smallint         ISYSTAB.dbspace_id
count               unsigned bigint  ISYSTAB.count
creator             unsigned int     ISYSTAB.creator
table_page_count    int              ISYSTAB.table_page_count
ext_page_count      int              ISYSTAB.ext_page_count
commit_action       int              ISYSTAB.commit_action
share_type          int              ISYSTAB.share_type
object_id           unsigned bigint  ISYSTAB.object_id
last_modified_at    timestamp        ISYSTAB.last_modified_at
table_name          char(128)        ISYSTAB.table_name
table_type          tinyint          ISYSTAB.table_type
replicate           char(1)          ISYSTAB.replicate
server_type         tinyint          ISYSTAB.server_type
tab_page_list       long varbit      ISYSTAB.tab_page_list
ext_page_list       long varbit      ISYSTAB.ext_page_list
pct_free            unsigned int     ISYSTAB.pct_free
clustered_index_id  unsigned int     ISYSTAB.clustered_index_id
encrypted           char(1)          ISYSTAB.encrypted
last_modified_tsn   unsigned bigint  ISYSTAB.last_modified_tsn
file_id             smallint         ISYSTAB.dbspace_id
table_type_str      char(13)         .