SHOW ORGANIZATION ACCOUNTS

Lists all the accounts in your organization, excluding managed accounts.

See also:

SHOW REPLICATION ACCOUNTS, SHOW MANAGED ACCOUNTS

Syntax

SHOW ORGANIZATION ACCOUNTS [ HISTORY ] [ LIKE '<pattern>' ]
Copy

Parameters

HISTORY

Optionally includes dropped accounts that have not yet been deleted. The output of SHOW ORGANIZATION ACCOUNTS HISTORY includes additional columns related to dropped accounts.

Default: No value (dropped accounts are not included in the output)

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).

Usage Notes

  • Only organization administrators (users with the ORGADMIN role) can execute this SQL command.

  • 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.

Output

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

Column

Description

organization_name

Name of the organization.

account_name

User-defined name that identifies an account within the organization.

region_group

Region group where the account is located. Note: This column is only displayed for organizations that span multiple region groups.

snowflake_region

Snowflake Region where the account is located. A Snowflake Region is a distinct location within a cloud platform region that is isolated from other Snowflake Regions. A Snowflake Region can be either multi-tenant or single-tenant (for a Virtual Private Snowflake account).

edition

Snowflake Edition of the account.

account_url

Preferred Snowflake account URL that includes the values of organization_name and account_name.

created_on

Date and time when the account was created.

comment

Comment for the account.

account_locator

System-assigned identifier of the acccount.

account_locator_url

Legacy Snowflake account URL syntax that includes the region_name and account_locator.

managed_accounts

Indicates how many managed accounts have been created by the account.

consumption_billing_entity_name

Name of the consumption billing entity.

marketplace_consumer_billing_entity_name

Name of the marketplace consumer billing entity.

marketplace_provider_billing_entity_name

Name of the marketplace provider billing entity.

old_account_url

If the original account URL was saved when the account was renamed, provides the original URL. If the original account URL was dropped, the value is NULL even if the account was renamed.

is_org_admin

Indicates whether the ORGADMIN role is enabled in an account. If TRUE, the role is enabled.

dropped_on [1]

Date and time when the account was last dropped.

scheduled_deletion_time [1]

Date and time when the account is scheduled to be permanently deleted. Accounts are deleted within one hour after the scheduled time.

restored_on [1]

Date and time when the account was last restored.

account_old_url_saved_on

If the original account URL was saved when the account was renamed, provides the date and time when the original account URL was saved.

account_old_url_last_used

If the original account URL was saved when the account was renamed, indicates the last time the account was accessed using the original URL.

organization_old_url

If the account’s organization was changed in a way that created a new account URL and the original account URL was saved, provides the original account URL. If the original account URL was dropped, the value is NULL even if the organization changed.

organization_old_url_saved_on

If the account’s organization was changed in a way that created a new account URL and the original account URL was saved, provides the date and time when the original account URL was saved.

organization_old_url_last_used

If the account’s organization was changed in a way that created a new account URL and the original account URL was saved, indicates the last time the account was accessed using the original account URL.

moved_to_organization [1]

If the account was moved to a different organization, provides the name of that organization.

moved_on [1]

Date and time when the account was moved to a different organization.

organization_URL_expiration_on [1]

If the account’s organization was changed in a way that created a new account URL and the original account URL was saved, provides the date and time when the original account URL will be dropped. Dropped URLs cannot be used to access the account.

is_events_account

Indicates whether an account is an events account. For more information, see Set up logging and event sharing for an application.

Examples

Show all the accounts whose name starts with myaccount:

SHOW ORGANIZATION ACCOUNTS LIKE 'myaccount%';
Copy