# SHOW 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 ``` #### 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 ``` #### 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. #### Syntax ``` SHOW FUNCTIONS [DETAILS] ``` #### Example ``` SHOW FUNCTIONS Scalar UDF distance_point_line ST_DWithin_Polygon_Polygon ST_Distance_Point_ClosedLineString Truncate ct_device_selection_udf_any area_triangle _h3RotatePent60cw ST_Intersects_Polygon_Point ST_DWithin_LineString_Polygon ST_Intersects_Point_Polygon box_contains_box ``` ## SHOW POLICIES 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 ; ``` ## 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. To interrupt a query in the queue, see [KILL QUERY](/sql/data-manipulation-dml/sql-capabilities#kill-query). ## 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 ``` 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 [, , ...] ``` #### Examples Show details for all tables you have access to: ``` omnisql> show table details; table_id|table_name |column_count|is_sharded_table|shard_count|max_rows |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count 1 |heavyai_states |11 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4082 |1 |536870912 |256 |242 2 |heavyai_counties |13 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |NULL |1 |536870912 |256 |NULL 3 |heavyai_countries|71 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4022 |1 |536870912 |256 |182 ``` Show details for table `omnisci_states`: ``` omnisql> show table details heavyai_states; table_id|table_name |column_count|is_sharded_table|shard_count|max_rows |fragment_size|max_rollback_epochs|min_epoch|max_epoch|min_epoch_floor|max_epoch_floor|metadata_file_count|total_metadata_file_size|total_metadata_page_count|total_free_metadata_page_count|data_file_count|total_data_file_size|total_data_page_count|total_free_data_page_count 1 |heavyai_states|11 |false |0 |4611686018427387904|32000000 |-1 |1 |1 |0 |0 |1 |16777216 |4096 |4082 |1 |536870912 |256 |242 ``` 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. ``` SHOW TABLE FUNCTIONS; tf_compute_dwell_times tf_feature_self_similarity tf_feature_similarity tf_rf_prop tf_rf_prop_max_signal tf_geo_rasterize_slope tf_geo_rasterize generate_random_strings generate_series tf_mandelbrot_cuda_float tf_mandelbrot_cuda tf_mandelbrot_float tf_mandelbrot ``` For more information, see [System Table Functions](/sql/data-manipulation-dml/sql-capabilities/show#system-table-functions). ## SHOW TABLE FUNCTIONS DETAILS Show detailed output information for the specified table function. Output details vary depending on the table function specified. #### Syntax ``` SHOW TABLE FUNCTIONS DETAILS ``` #### Example - generate\_series View SHOW output for the `generate_series` table function: ![](https://files.buildwithfern.com/heavyai.docs.buildwithfern.com/heavyai/489fcc1892458548686b2ab1e374a4418fd61479838616fd3d26d2a801a91d71/docs/assets/4_sqlcapabilities_1.png)
Output HeaderOutput Details
namegenerate_series
signature(i64 series_start, i64 series_stop, i64 series_step)

(i64 series_start, i64 series_stop) -> Column
input_namesseries_start, series_stop, series_step

series_start, series_stop
input_typesi64
output_namesgenerate_series
output_typesColumn i64
CPUtrue
GPUtrue
runtimefalse
filter_table_transposefalse
## SHOW SERVERS 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: ```sql 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**: ```plsql 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: ```sql heavysql> show user details ue, ud, uf, ua; User "ua" not found. ``` Show user details for specified users **ue**, **ud**, and **uf**: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql heavysql> show user details ua, ub, uc; User "uc" not found. ``` Show user details for user **ua**: ```sql 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. To see the queries in the queue, use the [SHOW QUERIES](/sql/data-manipulation-dml/sql-capabilities#show-queries-example) command: ```sql show queries; query_session_id|current_status |executor_id|submitted |query_str |login_name|client_address |db_name|exec_device_type 713-t1ax |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU 491-xpfb |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |Patrick |http:::1 |omnisci|GPU 451-gp2c |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU 190-5pax |PENDING_EXECUTOR |1 |2021-08-03 ...|SELECT ... |Cavin |http:::1 |omnisci|GPU 720-nQtV |RUNNING_QUERY_KERNEL|2 |2021-08-03 ...|SELECT ... |Cavin |tcp:::ffff:127.0.0.1:50142|omnisci|GPU 947-ooNP |RUNNING_IMPORTER |0 |2021-08-03 ...|IMPORT_GEO_TABLE|Rio |tcp:::ffff:127.0.0.1:47314|omnisci|CPU ``` To interrupt the last query in the list (ID `946-ooNP`): ```sql kill query '946-ooNP' ``` Showing the queries again indicates that `946-ooNP` has been deleted: ```sql show queries; query_session_id|current_status |executor_id|submitted |query_str |login_name|client_address |db_name|exec_device_type 713-t1ax |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU 491-xpfb |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |Patrick |http:::1 |omnisci|GPU 451-gp2c |PENDING_QUEUE |0 |2021-08-03 ...|SELECT ... |John |http:::1 |omnisci|GPU 190-5pax |PENDING_EXECUTOR |1 |2021-08-03 ...|SELECT ... |Cavin |http:::1 |omnisci|GPU 720-nQtV |RUNNING_QUERY_KERNEL|2 |2021-08-03 ...|SELECT ... |Cavin |tcp:::ffff:127.0.0.1:50142|omnisci|GPU ``` * 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.)