> For the complete documentation index, see [llms.txt](https://docs.deltastream.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.deltastream.io/reference/sql-syntax/command/list-queries.md). # 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 | +--------------------------------------+-----------------+---------------+-------------------------------+----------+-------------------------------+-------------------------------+ ```