# LIST QUERIES ## Syntax ```sql [ LIST | SHOW ] [ INTERACTIVE ] QUERIES [ WITH ( list_parameter [, ... ] ) ]; ``` ## Description This command provides a list with a [streaming or continuous query](/overview/core-concepts/queries.md#_streaming_or_continuous_query) or queries submitted to the current organization, including any active interactive query. See the [USE](/reference/sql-syntax/command/use.md) command to change the organization from which to `LIST QUERIES`. When queries are terminated/`STOPPED` and cleaned up, DeltaStream removes them from the list of active queries. Failed queries remain in the `ERRORED` state until they are explicitly terminated using the\ [TERMINATE QUERY](/reference/sql-syntax/query/terminate-query.md) statement. {% hint style="info" %} **Note** See [RESTART QUERY](/reference/sql-syntax/query/restart-query.md) for recovering from `ERRORED` queries. {% endhint %} Queries are listed only if the current role has `USAGE` privileges. ### Arguments #### WITH ( list\_parameter \[, ... ] ) This clause specifies [#list-parameters](#list-parameters "mention"). ### List Parameters | Parameter Name | Description | | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- | | `all` |

A flag that shows queries in any state, including terminated ones.


Required: No

Type: Flag

| ## Examples #### List active queries in the current organization The following shows a list of queries that are active in the `myorg` organization: ```sh demodb.public/demostore# LIST ORGANIZATIONS; +--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+ | ID | Name | Is Current | Is Default | Description | Profile Image Uri | Created At | +======================================+==========+=============+=============+==============+====================+===============================+ | caab10ef-5c55-4e6b-906b-29a9dae9b74d | myorg | true | true | | | 2024-07-02 21:16:22 +0000 UTC | +--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+ | 1fc8845b-8a35-452d-961a-91814e0ba3c0 | mr_prod | false | false | | | 2023-09-19 16:17:03 +0000 UTC | +--------------------------------------+----------+-------------+-------------+--------------+--------------------+-------------------------------+ demodb.public/demostore# LIST QUERIES; +--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+ | ID | Name | Version | Intended State | Actual State | Query | Owner | Created At | Updated At | +======================================+-----------+----------+=================+===============+==========================================+==========+===============================+===============================+ | e4353d8a-5187-4bc7-a2a0-684a4dbe51b1 | | 1 | running | running | CREATE MATERIALIZED VIEW mviewstream AS | sysadmin | 2024-06-25 22:08:25 +0000 UTC | 2024-06-25 22:24:05 +0000 UTC | | | | | | | SELECT upper(pageid) as pageidupper, | | | | | | | | | | pageid, count(userid) as cnt FROM pv | | | | | | | | | | GROUP BY pageid; | | | | +--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+ | c7c813cd-e5d7-4270-bd33-0a7d939d58da | | 1 | running | running | BEGIN APPLICATION some_app CREATE | sysadmin | 2024-03-19 03:29:27 +0000 UTC | 2024-05-01 17:46:19 +0000 UTC | | | | | | | VIRTUAL STREAM virtual.public.vx_mr AS | | | | | | | | | | SELECT * FROM pv WHERE userid = | | | | | | | | | | 'User_1'; INSERT INTO | | | | | | | | | | virtual.public.vx_mr SELECT * FROM pv | | | | | | | | | | WHERE userid <> 'User_1'; CREATE STREAM | | | | | | | | | | pouria_app_sink AS SELECT * FROM | | | | | | | | | | virtual.public.vx_mr; END APPLICATION; | | | | +--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+ | 3cd847cd-82bf-4d8a-80fd-772d5c6a0aa2 | | 1 | running | running | CREATE TABLE pv_agg WITH ( 'store' = | sysadmin | 2024-01-11 22:23:30 +0000 UTC | 2024-01-17 23:09:59 +0000 UTC | | | | | | | 'databricks_us_west_2', | | | | | | | | | | 'databricks.catalog.name' = | | | | | | | | | | 'deltastream_databricks1', | | | | | | | | | | 'databricks.schema.name' = '"default"', | | | | | | | | | | 'databricks.table.name' = 'pv_agg', | | | | | | | | | | 'table.data.file.location' = | | | | | | | | | | 's3://deltastream-mr-databricks-bucket2/ | | | | | | | | | | test/0/pv_agg' ) AS SELECT userid, | | | | | | | | | | count(pageid) AS pagecount FROM | | | | | | | | | | pageviews GROUP BY userid; | | | | +--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+ | c97ff2a2-2a95-4f44-82b4-ef48c0456d1e | | 1 | running | running | CREATE TABLE "TOTAL_PV" WITH ( | sysadmin | 2023-09-19 20:49:20 +0000 UTC | 2023-10-02 19:06:27 +0000 UTC | | | | | | | 'snowflake.db.name' = | | | | | | | | | | 'DELTA_STREAMING', | | | | | | | | | | 'snowflake.schema.name' = 'PUBLIC') AS | | | | | | | | | | SELECT userid AS "USERID", COUNT(*) AS | | | | | | | | | | "VIEWS" FROM pageviews GROUP BY userid; | | | | +--------------------------------------+-----------+----------+-----------------+---------------+------------------------------------------+----------+-------------------------------+-------------------------------+ ``` #### List active interactive queries in a specific organization The following lists interactive queries in a different organization, `Shared org`, using its unique identifier from the list of organizations the user owns: ```sh demodb.public/demostore# USE ORGANIZATION "Shared org"; +--------------+------------+------------+-------------------------------+ | Type | Name | Command | Summary | +==============+============+============+===============================+ | organization | Shared org | USE | using organization Shared org | +--------------+------------+------------+-------------------------------+ bzdb.public/sharedstore# LIST INTERACTIVE QUERIES; +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ | ID | Intended State | Actual State | Query | Owner | Created At | Updated At | +======================================+=================+===============+===============================+==========+===============================+===============================+ | fa86821e-a3dc-431c-bbaa-3f330bae2a5f | running | new | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:08:25 +0000 UTC | +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ ``` Only active queries display in the list of queries. If the above interactive query is stopped, it is not returned in the list after it’s cleaned up: ```sh bzdb.public/sharedstore# STOP SANDBOX; +---------+---------+----------+------------------------------------------+ | Type | Name | Command | Summary | +=========+=========+==========+==========================================+ | sandbox | sandbox | DROP | sandbox was successfully marked for stop | +---------+---------+----------+------------------------------------------+ bzdb.public/sharedstore# LIST INTERACTIVE QUERIES; +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ | ID | Intended State | Actual State | Query | Owner | Created At | Updated At | +======================================+=================+===============+===============================+==========+===============================+===============================+ | fa86821e-a3dc-431c-bbaa-3f330bae2a5f | stopped | running | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:15:54 +0000 UTC | +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ bzdb.public/sharedstore# LIST INTERACTIVE QUERIES; +-----+-----------------+---------------+--------+--------+-------------+-------------+ | ID | Intended State | Actual State | Query | Owner | Created At | Updated At | +=====+=================+===============+========+========+=============+=============+ +-----+-----------------+---------------+--------+--------+-------------+-------------+ ``` #### List all interactive queries in the current organization When a query is terminated and cleaned up, you can access it using the `all` parameter: ```sh bzdb.public/sharedstore# LIST INTERACTIVE QUERIES ('all'); +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ | ID | Intended State | Actual State | Query | Owner | Created At | Updated At | +======================================+=================+===============+===============================+==========+===============================+===============================+ | fa86821e-a3dc-431c-bbaa-3f330bae2a5f | stopped | stopped | SELECT * FROM interest_count; | sysadmin | 2023-02-21 22:08:25 +0000 UTC | 2023-02-21 22:15:59 +0000 UTC | +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.deltastream.io/reference/sql-syntax/command/list-queries.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.