Use SHOW commands to get information about databases, tables, and user sessions.
SHOW CREATE SERVER
Shows the CREATE SERVER statement that could have been used to create the server.
Syntax
SHOW CREATE SERVER <servername>
Example
SHOW CREATE SERVER default_local_delimited;
create_server_sql
CREATE SERVER default_local_delimited FOREIGN DATA WRAPPER DELIMITED_FILE
WITH (STORAGE_TYPE='LOCAL_FILE');
SHOW CREATE TABLE
Shows the CREATE TABLE statement that could have been used to create the table.
Syntax
SHOW CREATE TABLE <tablename>
Example
SHOW CREATE TABLE heavyai_states;
CREATE TABLE heavyai_states (
id TEXT ENCODING DICT(32),
abbr TEXT ENCODING DICT(32),
name TEXT ENCODING DICT(32),
omnisci_geo GEOMETRY(MULTIPOLYGON, 4326
) NOT NULL);
SHOW DATABASES
Retrieve the databases accessible for the current user, showing the database name and owner.
Example
SHOW DATABASES
Database Owner
omnisci admin
2004_zipcodes admin
game_results jane
signals jason
...
SHOW FUNCTIONS
Show registered compile-time UDFs and extension functions in the system and their arguments.
Displays a list of all row-level security (RLS) policies that exist for a user or role; admin rights are required. If EFFECTIVE is used, the list also includes any policies that exist for all roles that apply to the requested user or role.
Syntax
SHOW [EFFECTIVE] POLICIES <name>;
SHOW QUERIES
Returns a list of queued queries in the system; information includes session ID, status, query string, account login name, client address, database name, and device type (CPU or GPU).
Example
show queries;
query_session_id|current_status|submitted |query_str |login_name|client_address |db_name |exec_device_type
834-8VAA |Pending |2020-05-06 08:21:15|select d_date_sk, count(1) from date_dim group by d_date_sk;|admin |tcp:localhost:48596|tpcds_sf10|CPU
826-CLKk |Running |2020-05-06 08:20:57|select count(1) from store_sales, store_returns; |admin |tcp:localhost:48592|tpcds_sf10|CPU
828-V6s7 |Pending |2020-05-06 08:21:13|select count(1) from store_sales; |admin |tcp:localhost:48594|tpcds_sf10|GPU
946-rtJ7 |Pending |2020-05-06 08:20:58|select count(1) from item; |admin |tcp:localhost:48610|tpcds_sf10|GPU
Admin users can see and interrupt all queries, and non-admin users can see and interrupt only their own queries
NOTE: SHOW QUERIES is only available if the runtime query interrupt parameter (enable-runtime-query-interrupt) is set.
SHOW ROLES
If included with a name, lists the role granted directly to a user or role. SHOW EFFECTIVE ROLES with a name lists the roles directly granted to a user or role, and also lists the roles indirectly inherited through the directly granted roles.
Syntax
SHOW [EFFECTIVE] ROLES <name>
If the user name or role name is omitted, then a regular user sees their own roles, and a superuser sees a list of all roles existing in the system.
SHOW RUNTIME FUNCTIONS
Show user-defined runtime functions and table functions.
Syntax
SHOW RUNTIME [TABLE] FUNCTIONS
SHOW RUNTIME [TABLE] FUNCTION DETAILS
SHOW SUPPORTED DATA SOURCES
Show data connectors.
Syntax
show supported data sources
SHOW TABLE DETAILS
Displays storage-related information for a table, such as the table ID/name, number of data/metadata files used by the table, total size of data/metadata files, and table epoch values.
You can see table details for all tables that you have access to in the current database, or for only those tables you specify.
Syntax
SHOW TABLE DETAILS [<table-name>, <table-name>, ...]
The number of columns returned includes system columns. As a result, the number of columns in column_count can be up to two greater than the number of columns created by the user.
SHOW TABLE FUNCTIONS
Displays the list of available system (built-in) table functions.
Retrieve the servers accessible for the current user.
Example
SHOW SERVERS;
server_name|data_wrapper|created_at|options
default_local_delimited|DELIMITED_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
default_local_parquet|PARQUET_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
default_local_regex_parsed|REGEX_PARSED_FILE|2022-03-15 10:06:05|{"STORAGE_TYPE":"LOCAL_FILE"}
...
SHOW TABLES
Retrieve the tables accessible for the current user.
Example
SHOW TABLES;
table_name
----------
omnisci_states
omnisci_counties
omnisci_countries
streets_nyc
streets_miami
...
SHOW USER DETAILS
Lists name, ID, and default database for all or specified users for the current database. If the command is issued by a superuser, login permission status is also shown. Only superusers see users who do not have permission to log in.
Example
SHOW USER DETAILS
NAME ID DEFAULT_DB
mike.nuumann 191 mondale
Dale 184 churchill
Editor_Test 141 mondale
Jerry.wong 181 alluvial
AA_superuser 139
BB_superuser 2140
PlinyTheElder 183 windsor
aaron.tyre 241 db1
achristie 243 sid
eve.mandela 202 nancy
...
SHOW [ALL] USER DETAILS lists name, ID, superuser status, default database, and login permission status for all users across the HeavyDB instance. This variant of the command is available only to superusers. Regular users who run the SHOW ALL USER DETAILS command receive an error message.
Superuser Output
Show all user details for all users:
heavysql> show all user details;
NAME|ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
admin|0|true|(-1)|true
ua|2|false|db1(2)|true
ub|3|false|db1(2)|true
uc|4|false|db1(2)|false
ud|5|false|db2(3)|true
ue|6|false|db2(3)|true
uf|7|false|db2(3)|false
Show all user details for specified users ue, ud, ua, and uf:
heavysql> \db db2
User admin switched to database db2
heavysql> show all user details ue, ud, uf, ua;
NAME|ID|IS_SUPER|DEFAULT_DB|CAN_LOGIN
ua|2|false|db1(2)|true
ud|5|false|db2(3)|true
ue|6|false|db2(3)|true
uf|7|false|db2(3)|false
If a specified user is not found, the superuser sees an error message:
heavysql> show user details ue, ud, uf, ua;
User "ua" not found.
Show user details for specified users ue, ud, and uf:
heavysql> show user details ue, ud, uf;
NAME|ID|DEFAULT_DB|CAN_LOGIN
ud|5|db2(3)|true
ue|6|db2(3)|true
uf|7|db2(3)|false
Show user details for all users:
heavysql> show user details;
NAME|ID|DEFAULT_DB|CAN_LOGIN
ud|5|db2(3)|true
ue|6|db2(3)|true
uf|7|db2(3)|false
Non-Superuser Output
Running SHOW ALL USER DETAILS results in an error message:
heavysql> \db
User ua is using database db1
heavysql> show all user details;
SHOW ALL USER DETAILS is only available to superusers. (Try SHOW USER DETAILS instead?)
Show user details for all users:
heavysql> show user details;
NAME|ID|DEFAULT_DB
ua|2|db1
ub|3|db1
If a specified user is not found, the user sees an error message:
heavysql> show user details ua, ub, uc;
User "uc" not found.
Show user details for user ua:
heavysql> show user details ua;
NAME|ID|DEFAULT_DB
ua|2|db1
SHOW USER SESSIONS
Retrieve all persisted user sessions, showing the session ID, user login name, client address, and database name. Admin or superuser privileges required.
SHOW USER SESSIONS;
session_id login_name client_address db_name
453-X6ds mike http:198.51.100.1 game_results
453-0t2r erin http:198.51.100.11 game_results
421-B64s shauna http:198.51.100.43 game_results
213-06dw ahmed http:198.51.100.12 signals
333-R28d cat http:198.51.100.233 signals
497-Xyz6 inez http:198.51.100.5 ships
...
KILL QUERY
Interrupt a queued query. Specify the query by using its session ID.
KILL QUERY is only available if the runtime query interrupt parameter (enable-runtime-query-interrupt) is set.
Interrupting a query in ‘PENDING_QUEUE’ status is supported in both distributed and single-server mode.
To enable query interrupt for tables imported from data files in local storage, set enable_non_kernel_time_query_interrupt to TRUE. (It is enabled by default.)
