For DBAs used to Oracle sqlplus, not being able to do a “describe my_view” via hdbsql can sometimes be frustrating. This is especially frustrating since it is possible to do it for a table.
In order to overcome this lack, I have written a small procedure which allows to reproduce almost the same result as the “\dc” command for a table.
This is still more constraining than a simple “describe” or “desc”, but it avoids having to manually query the “VIEW_COLUMNS” view or search the SAP documentation to get the view definition.
How it works
The procedure will simply query the “VIEW_COLUMNS” view to build the result. If it is called with a schema name in parameter (for example: MYDBAWORLD.TABLES”), it will be taken into account in the search. Otherwise, the procedure will search for a corresponding view in the “SYS” schema.
Once created, you can simply call it this way:
call DESCRIBE('MYDBAWORLD.TEST_VIEW');
Or, for system views:
call DESCRIBE('TABLES');
By default, it will only be accessible by its creator, but you can grant the execution rights to “PUBLIC” and create a public synonym if you want to make it accessible to all users whitout having to specify the schema name:
grant execute on DESCRIBE to public;
create public synonym DESCRIBE for DESCRIBE;
The procedure
CREATE OR REPLACE PROCEDURE DESCRIBE(VIEW_NAME NVARCHAR(513))
LANGUAGE SQLSCRIPT AS
BEGIN
USING SQLSCRIPT_STRING AS LIB;
DECLARE v_schema VARCHAR(256);
DECLARE v_name VARCHAR(256);
DECLARE v_tempo VARCHAR(512);
v_tempo:=REPLACE(:VIEW_NAME,'.','');
IF VIEW_NAME <> v_tempo THEN
(v_schema, v_name) = LIB:SPLIT(:VIEW_NAME,'.');
ELSE
v_schema='SYS';
v_name:=VIEW_NAME;
END IF;
SELECT
COLUMN_NAME "Column Name",
DATA_TYPE_NAME "Type",
LENGTH "Length",
IS_NULLABLE "Nullable"
FROM
SYS.VIEW_COLUMNS
WHERE
SCHEMA_NAME= :v_schema
AND
VIEW_NAME= :v_name
ORDER BY POSITION ASC;
END;
Some examples
For tables (whitout using the procedure):
hdbsql HXE=> \pa
Page by page scroll output switched OFF
hdbsql HXE=> \al
Aligned output mode switched ON
hdbsql HXE=> \dc MYDBAWORLD.TEST_TABLE
Table "MYDBAWORLD.TEST_TABLE"
| Column Name | Type | Le | Nul | Key |
| ---------------- | -------- | -- | --- | --- |
| ACCOUNT_ID | INTEGER | 10 | YES | n/a |
| ACCOUNT_OWNER_ID | NVARCHAR | 10 | YES | n/a |
| ACCOUNT_BALANCE | DOUBLE | 15 | YES | n/a |
For views:
hdbsql HXE=> call describe('MYDBAWORLD.TEST_VIEW');
| Column Name | Type | Length | Null |
| ---------------- | -------- | ----------- | ---- |
| ACCOUNT_ID | INTEGER | 10 | TRUE |
| ACCOUNT_OWNER_ID | NVARCHAR | 10 | TRUE |
| ACCOUNT_BALANCE | DOUBLE | 15 | TRUE |
3 rows selected (overall time 2181 usec; server time 782 usec)
Without specifying the schema (default search in the “SYS” schema):
hdbsql HXE=> call describe('TABLES');
| Column Name | Type | Length | Nulla |
| ------------------------------ | --------- | ----------- | ----- |
| SCHEMA_NAME | NVARCHAR | 256 | TRUE |
| TABLE_NAME | NVARCHAR | 256 | TRUE |
| TABLE_OID | BIGINT | 19 | FALSE |
| COMMENTS | NVARCHAR | 5000 | TRUE |
| FIXED_PART_SIZE | SMALLINT | 5 | TRUE |
| IS_LOGGED | VARCHAR | 5 | TRUE |
| IS_SYSTEM_TABLE | VARCHAR | 5 | FALSE |
| IS_COLUMN_TABLE | VARCHAR | 5 | FALSE |
| TABLE_TYPE | VARCHAR | 16 | FALSE |
| IS_INSERT_ONLY | VARCHAR | 5 | FALSE |
| IS_TENANT_SHARED_DATA | VARCHAR | 5 | FALSE |
| IS_TENANT_SHARED_METADATA | VARCHAR | 5 | FALSE |
| SESSION_TYPE | VARCHAR | 7 | FALSE |
| IS_TEMPORARY | VARCHAR | 5 | FALSE |
| TEMPORARY_TABLE_TYPE | VARCHAR | 10 | FALSE |
| COMMIT_ACTION | VARCHAR | 8 | TRUE |
| IS_USER_DEFINED_TYPE | VARCHAR | 5 | FALSE |
| HAS_PRIMARY_KEY | VARCHAR | 5 | FALSE |
| PARTITION_SPEC | NCLOB | 2147483647 | TRUE |
| USES_EXTKEY | VARCHAR | 5 | TRUE |
| AUTO_MERGE_ON | VARCHAR | 5 | TRUE |
| USES_DIMFN_CACHE | VARCHAR | 5 | TRUE |
| IS_PUBLIC | VARCHAR | 5 | TRUE |
| AUTO_OPTIMIZE_COMPRESSION_ON | VARCHAR | 5 | TRUE |
| COMPRESSED_EXTKEY | VARCHAR | 5 | TRUE |
| HAS_TEXT_FIELDS | VARCHAR | 5 | TRUE |
| USES_QUEUE_TABLE | VARCHAR | 5 | TRUE |
| IS_PRELOAD | VARCHAR | 5 | TRUE |
| IS_PARTIAL_PRELOAD | VARCHAR | 5 | TRUE |
| UNLOAD_PRIORITY | TINYINT | 3 | TRUE |
| HAS_SCHEMA_FLEXIBILITY | VARCHAR | 5 | TRUE |
| IS_REPLICA | VARCHAR | 5 | FALSE |
| HAS_STRUCTURED_PRIVILEGE_CHECK | VARCHAR | 5 | FALSE |
| IS_SERIES_TABLE | VARCHAR | 5 | FALSE |
| ROW_ORDER_TYPE | VARCHAR | 8 | TRUE |
| CREATE_TIME | TIMESTAMP | 27 | TRUE |
| TEMPORAL_TYPE | VARCHAR | 8 | TRUE |
| HAS_MASKED_COLUMNS | VARCHAR | 5 | FALSE |
| MASK_MODE | VARCHAR | 12 | TRUE |
| PERSISTENT_MEMORY | VARCHAR | 5 | TRUE |
| HAS_RECORD_COMMIT_TIMESTAMP | VARCHAR | 5 | FALSE |
| IS_REPLICATION_LOG_ENABLED | VARCHAR | 5 | FALSE |
| NUMA_NODE_INDEXES | NVARCHAR | 5000 | TRUE |
| IS_MOVABLE | VARCHAR | 5 | FALSE |
| LOAD_UNIT | VARCHAR | 7 | TRUE |
45 rows selected (overall time 2725 usec; server time 943 usec)
You can of course change the name of the procedure (simply “DESC” for example) or create a second synonym:
create public synonym DESC for DESCRIBE;
I hope you find this little trick useful. Stay tuned for more DBA stuff 🙂
