Command Line HDBSQL \D commands in SAP Console

[P281512]

It pays to know the HANA database system tables and views.
Not unsurprisingly all databases HANA, MSSQLServer, Oracle have similar metadata and HANA  System tables and views well documented.

hdbsql is the Command line utility of HANA and is immensely useful and powerful.

\mu Multi Line SQL is  not default; I always us this as first command
because very often you want to write neat formatted SQLs in more than 1 line

\h Help
\s[tatus] print out host, database, user etc.

The Wonderful \d commands I miss in SQL Console
\dc [PATTERN] list columns
\de [PATTERN] list indices
\dp [PATTERN] list procedures
\ds [NAME] list schemas
\dt [PATTERN] list tables
\du [NAME] list users
\dv [PATTERN] list views

I traced hdbsql and found out the SQLs!

The best training on HANA you can get is by the series by Dan van Leeuwen
https://developers.sap.com/tutorials/hana-clients-choose-hana-instance.html
https://developers.sap.com/tutorials/hana-clients-install.html
https://developers.sap.com/tutorials/hana-clients-hdbsql.html
https://developers.sap.com/tutorials/hana-clients-node.html
https://developers.sap.com/tutorials/hana-clients-python.html
https://developers.sap.com/tutorials/hana-clients-jdbc.html
https://developers.sap.com/tutorials/hana-clients-odbc.html
https://developers.sap.com/tutorials/hana-clients-dot-net-core.html
https://developers.sap.com/tutorials/hana-clients-entity-framework.html
https://developers.sap.com/tutorials/hana-clients-golang.html
https://developers.sap.com/tutorials/hana-clients-trace.html
https://developers.sap.com/tutorials/hana-clients-cf.html
https://developers.sap.com/tutorials/hana-clients-x509.html

Used the trace link above to switch ON and OFF
hdbsqldbc_cons SHOW ALL # to see location of trace
## tells you the file name /tmp/traces/SQLDBC-%pid%.txt

hdbsqldbc_cons SQL ON # to switch on SQL trace

hdbsqldbc_cons TRACE OFF # to switch off SQL trace

-- SQL COMMAND: \s
SELECT
user_name,
C.system_id,
C.database_name
FROM
PUBLIC.M_CONNECTIONS AS A
INNER JOIN
PUBLIC.M_SERVICES AS B
ON A.host = B.host

-- SQL COMMAND:
SELECT VERSION
FROM SYS.M_DATABASE;

-- SQL COMMAND:
SELECT B.HOST || ':' || SQL_PORT
FROM
PUBLIC.M_CONNECTIONS AS A
INNER JOIN
SYS.M_SERVICES AS B
ON A.HOST = B.HOST
AND A.port = B.port
WHERE OWN = 'TRUE';

-- DO NOT use % but %LETTERS% ex  M_%  or %MODEL%
-- I have used % as place holder;  please restrict as you need

-- MOST IMPORTANT TABLE/VIEW is SYS.OBJECTS
-- esp. to disambiguate TABLEs and VIEWs
SELECT *
FROM sys.objects
WHERE schema_name LIKE '%'
AND object_name LIKE '%'
ORDER BY
schema_name ASC,
object_name ASC,
object_type ASC;

 

-- SQL COMMAND: \dp
SELECT
SCHEMA_NAME AS "Schema",
PROCEDURE_NAME AS "Name",
PROCEDURE_TYPE AS "Type"
FROM SYS.PROCEDURES
WHERE SCHEMA_NAME LIKE '%'
AND PROCEDURE_NAME LIKE '%'
ORDER BY
"Schema" ASC,
"Name" ASC,
"Type" ASC;

-- SQL COMMAND: \dt
SELECT
SCHEMA_NAME AS "Schema",
table_name AS "Name"
FROM SYS.tables
WHERE SCHEMA_NAME LIKE '%'
AND table_name LIKE '%'
ORDER BY
"Schema" ASC,
"Name" ASC;

-- SQL COMMAND : \dc for TABLES
SELECT
schema_name,
table_name,
column_name AS "Column Name",
data_type_name AS "Type",
map( length, NULL, '-', TO_CHAR(length) ) || map( scale, NULL, '', 0, '', ',' || TO_CHAR(scale) ) AS "Length",
map( is_nullable, 'TRUE', 'YES', 'NO' ) AS "Nullable",
'n/a' AS "Keypos"
FROM SYS.TABLE_COLUMNS
WHERE SCHEMA_NAME LIKE '%'
AND table_name LIKE '%'
ORDER BY
schema_name ASC,
table_name ASC,
position ASC;

- SQL COMMAND : \de
SELECT
INDEX_NAME AS "Index Name",
TABLE_NAME || '.' || COLUMN_NAME AS "Table.Column",
map( CONSTRAINT, 'UNIQUE', 'true', 'false' ) AS "Unique",
TO_CHAR(POSITION) AS "POS",
ASCENDING_ORDER AS "Sort"
FROM "SYS"."INDEX_COLUMNS"
WHERE SCHEMA_NAME LIKE '%'
AND TABLE_NAME LIKE '%'
ORDER BY
INDEX_NAME ASC,
POSITION ASC;

-- SQL COMMAND : \ds
SELECT
SCHEMA_NAME AS "Schema name",
SCHEMA_OWNER AS "Owner name"
FROM SYS.SCHEMAS
WHERE SCHEMA_NAME LIKE '%'
ORDER BY
SCHEMA_NAME ASC,
SCHEMA_OWNER ASC;

-- SQL COMMAND : \dv
SELECT
SCHEMA_NAME AS "Schema",
view_name AS "Name"
FROM SYS.views
WHERE SCHEMA_NAME LIKE '%'
AND view_name LIKE '%'
ORDER BY
"Schema" ASC,
"Name" ASC;

-- \dc for VIEWS
-- idea From https://www.mydbaworld.com/sap-hana-describe-views-in-hdbsql/
SELECT
schema_name,
view_name,
column_name AS "Column Name",
data_type_name AS "Type",
map( length, NULL, '-', TO_CHAR(length) ) || map( scale, NULL, '', 0, '', ',' || TO_CHAR(scale) ) AS "Length",
map( is_nullable, 'TRUE', 'YES', 'NO' ) AS "Nullable",
'n/a' AS "Keypos"
FROM SYS.VIEW_COLUMNS
WHERE SCHEMA_NAME LIKE '%'
AND VIEW_name LIKE '%'
ORDER BY
schema_name ASC,
view_name ASC,
position ASC;

 

-- SQL COMMAND : \du
SELECT
USER_NAME AS "User name",
USER_MODE AS "User mode"
FROM sys.users
WHERE USER_NAME LIKE '%'
ORDER BY USER_NAME ASC;

 

\dp was my favourite to study where APL procedures are being stored
I needed \dp %.%MODEL_% in SAP HANA trial of BTP

-- Another useful SQL is "LIST PLUGINS"
SELECT
PLUGIN_NAME,
VALUE
FROM M_PLUGIN_MANIFESTS
WHERE KEY = 'fullversion';

The above lists plugins in HANA EXPRESS after installing apl.tgz

1;AFL ;2.00.054.0000 Build 1611928859-1530
2;EPMMDS ;1.00.202102.02.1611928859
3;SAP_AFL_SDK_APL;4.200.1911.0
The names are TECHNICAL NAMES

The LifeCycle Manger gives User Friendly names
Software Components
SAP HANA AFL (incl.PAL,BFL,OFL)
Version 2.00.054.0000.1611928859
SAP HANA EPM-MDS
Version 2.00.054.0000.1611928859
Automated Predictive Library
Version 4.200.1911.0.0
SAP HANA Database
Version 2.00.054.00.1611906357

I do hope that \d SQLs I have collected will prove very useful to anyone using Hana SQL Console from Hana Studio whether the Cloud Version or Eclipse version

These SQLs help you navigate SAP System Tables to explore the metadata