Wednesday, January 26, 2011

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

2 comments:

Justin Willey said...

Very handy. I've tended to be put using off using the DESCRIBE() (and the also useful REWRITE() function) for long complex queries by the need to first rewrite the queries doubling up quotes slashes etc (and half the time introducing errors) - am I missing an easy way of doing that?

Breck Carter said...

Try putting the query in a text file, then calling xp_read_file() as argument to the function.