Did you know?

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

?

1

<br>CREATE TABLE t (<br>   pkey   INTEGER           NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,<br>   data1  VARCHAR ( 100 )   NOT NULL DEFAULT '',<br>   data2  DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,<br>   data3  LONG BINARY       NOT NULL DEFAULT '' );<br><br>DESCRIBE TABLE t;<br><br>Column  Type          Nullable  Primary Key<br>pkey    integer          0           1<br>data1   varchar(100)     0           0<br>data2   decimal(11,2)    0           0<br>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?

?

1

<br>SELECT * FROM sa_describe_query ( 'SELECT * FROM t', 1 );<br><br>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<br>      1       pkey      2     int         int                         4     0            4       (NULL)         (NULL)            t            723            1            DBA                t                pkey          false            true           true             false<br>      2       data1     9     varchar     varchar(100)              100     0          100       (NULL)         (NULL)            t            723            2            DBA                t                data1         false            false          false            false<br>      3       data2    27     decimal     decimal(11,2)              11     2           11       (NULL)         (NULL)            t            723            3            DBA                t                data2         false            false          false            false<br>      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):

?

1

<br>CREATE PROCEDURE p ( IN @query LONG VARCHAR )<br>BEGIN<br>   SELECT name                  AS column_name,<br>          domain_name_with_size AS data_type<br>     FROM sa_describe_query ( @query, 1 )<br>    ORDER BY column_number;<br>END;<br><br>SELECT * FROM p ( 'SELECT * FROM t' );<br><br>column_name  data_type<br>pkey         int<br>data1        varchar(100)<br>data2        decimal(11,2)<br>data3        long binary

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

?

1

<br>CREATE VIEW v AS<br>SELECT data1,<br>       data2 * 10,<br>       pkey<br>  FROM t;<br><br>SELECT name                                              AS view_column_name,<br>       domain_name_with_size                             AS data_type,<br>       STRING ( base_table_name, '.', base_column_name ) AS base_column <br>  FROM sa_describe_query ( 'SELECT * FROM v' )<br> ORDER BY column_number;<br><br>view_column_name  data_type      base_column<br>data1             varchar(100)   t.data1<br>expression        numeric(13,2)  .<br>pkey              int            t.pkey<br><br>SELECT name                                              AS view_column_name,<br>       domain_name_with_size                             AS data_type,<br>       STRING ( base_table_name, '.', base_column_name ) AS base_column <br>  FROM sa_describe_query ( 'SELECT * FROM SYSTAB' )<br> ORDER BY column_number;<br><br>view_column_name    data_type        base_column<br>table_id            unsigned int     ISYSTAB.table_id<br>dbspace_id          smallint         ISYSTAB.dbspace_id<br>count               unsigned bigint  ISYSTAB.count<br>creator             unsigned int     ISYSTAB.creator<br>table_page_count    int              ISYSTAB.table_page_count<br>ext_page_count      int              ISYSTAB.ext_page_count<br>commit_action       int              ISYSTAB.commit_action<br>share_type          int              ISYSTAB.share_type<br>object_id           unsigned bigint  ISYSTAB.object_id<br>last_modified_at    timestamp        ISYSTAB.last_modified_at<br>table_name          char(128)        ISYSTAB.table_name<br>table_type          tinyint          ISYSTAB.table_type<br>replicate           char(1)          ISYSTAB.replicate<br>server_type         tinyint          ISYSTAB.server_type<br>tab_page_list       long varbit      ISYSTAB.tab_page_list<br>ext_page_list       long varbit      ISYSTAB.ext_page_list<br>pct_free            unsigned int     ISYSTAB.pct_free<br>clustered_index_id  unsigned int     ISYSTAB.clustered_index_id<br>encrypted           char(1)          ISYSTAB.encrypted<br>last_modified_tsn   unsigned bigint  ISYSTAB.last_modified_tsn<br>file_id             smallint         ISYSTAB.dbspace_id<br>table_type_str      char(13)         .