SHOW SERVICES¶

Lists the Snowpark Container Services services (including job services) for which you have access privileges.

The SHOW SERVICES output also includes services running as jobs (see EXECUTE JOB SERVICE).
SHOW JOB SERVICES provides only the list of services running as jobs.
SHOW SERVICES EXCLUDE JOBS output does not include services running as jobs.

See also:: CREATE SERVICE , ALTER SERVICE, DROP SERVICE , DESCRIBE SERVICE

Syntax¶

SHOW [ JOB ] SERVICES [ EXCLUDE JOBS ] [ LIKE '<pattern>' ]
           [ IN
                {
                  ACCOUNT                  |

                  DATABASE                 |
                  DATABASE <database_name> |

                  SCHEMA                   |
                  SCHEMA <schema_name>     |
                  <schema_name>            |

                  COMPUTE POOL <compute_pool_name>
                }
           ]
           [ STARTS WITH '<name_string>' ]
           [ LIMIT <rows> [ FROM '<name_string>' ] ]

Copy

Parameters¶

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...

... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

Default: Depends on whether the session currently has a database in use:

Database: DATABASE is the default (that is, the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

STARTS WITH 'name_string'

Optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case-sensitive.

For example, the following strings return different results:

... STARTS WITH 'B' ...

... STARTS WITH 'b' ...

. Default: No value (no filtering is applied to the output)

LIMIT rows

Optionally limits the maximum number of rows returned. The actual number of rows returned night be less than the specified limit. For example, the number of existing objects is less than the specified limit.

Default: No value (no limit is applied to the output).

Output¶

The command output provides service properties and metadata in the following columns:

Column	Description
`name`	Snowpark Container Services service name.
`database_name`	Database in which the service is created.
`schema_name`	Schema in which the service is created.
`owner`	Role that owns the service.
`compute_pool`	Compute pool name where Snowflake runs the service.
`dns_name`	Snowflake-assigned DNS name of the service. The DNS name enables service-to-service communications (see Tutorial 3).
`min_instances`	Minimum number of service instances Snowflake should run.
`max_instances`	Maximum number of service instances that Snowflake can scale when needed.
`auto_resume`	If `true`, Snowflake auto-resumes the service, if suspended, when service function is called or when an incoming request (ingres) is received (see Using a service).
`external_access_integrations`	List of external access integrations associated with the service. For more information, see Network egress.
`created_on`	Date and time when the service was created.
`updated_on`	Date and time when service is last updated.
`resumed_on`	Timestamp when the service was last resumed.
`comment`	Service related comment.
`owner_role_type`	The type of role that owns the object, either ROLE or DATABASE_ROLE.
`query_warehouse`	When a service container connects to Snowflake to execute a query and does not explicitly specify a warehouse to use, Snowflake uses this warehouse as default.
`is_job`	`true` if the service is a job service; `false` otherwise. SHOW JOB SERVICES and SHOW SERVICES EXCLUDE JOBS do not include this column in the output.

Access control requirements¶

A role used to execute this SQL command must have at least one of the following privileges at a minimum:

Privilege	Object	Notes
Any one of these privileges: OWNERSHIP, USAGE, MONITOR or OPERATE	Service

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

Columns that start with the prefix is_ return either Y (yes) or N (no).
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.

To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Examples¶

The following example lists services in the current database and schema for the session:

SHOW SERVICES;

Copy

The following example lists one service:

SHOW SERVICES LIMIT 1;

Copy

The following example lists services with names containing “echo”:

SHOW SERVICES LIKE '%echo%';

Copy

The following example lists one service with a name containing “echo”:

SHOW SERVICES LIKE '%echo%' LIMIT 1;

Copy

The following example lists only services running as a job:

SHOW JOB SERVICES;

Copy

Sample output:

+--------------------+---------------+-------------+----------+-----------------------+-----------------------------------------------------------------------+---------------+---------------+-------------+--------------------------------+-------------------------------+--------------------------------+------------+---------+-----------------+-----------------+
| name               | database_name | schema_name | owner    | compute_pool          | dns_name                                                              | min_instances | max_instances | auto_resume | external_access_integrations   | created_on                    | updated_on                     | resumed_on | comment | owner_role_type | query_warehouse |
|--------------------+---------------+-------------+----------+-----------------------+-----------------------------------------------------------------------+---------------+---------------+-------------+--------------------------------+-------------------------------+--------------------------------+------------+---------------------------+-----------------|
| ECHO_SERVICE       | TUTORIAL_DB   | DATA_SCHEMA | TST_ROLE | TUTORIAL_COMPUTE_POOL | echo-service.data-schema.tutorial-db.snowflakecomputing.internal      |             1 |             1 | true        | ["example_access_integration"] |2023-10-26 10:53:32.150 -0700  | 2023-10-31 23:56:49.041 -0700  | NULL       | NULL    | ROLE            | NULL            |
+--------------------+---------------+-------------+----------+-----------------------+-----------------------------------------------------------------------+---------------+---------------+-------------+--------------------------------+-------------------------------+--------------------------------+------------+---------+-----------------+-----------------+