SHOW GROUPS

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Lists the groups that match an optionally supplied regular expression pattern. If you don't supply a pattern, the command lists all of the groups in the system. You can optionally supply an identifier to show only the groups a specific user or group belongs to.

If a principal is provided using WITH {USER | GROUP}, a not null Boolean value in column directGroup indicates the principal's membership.

  • TRUE: The principal is a direct member of the group.
  • FALSE: The principal is an indirect member of the group.

If WITH {USER | GROUP} is not used, directGroup will always be NULL.

Syntax

SHOW GROUPS [ WITH USER user_principal |
              WITH GROUP group_principal ]
            [ [ LIKE ] regex_pattern ]

Parameters

  • user_principal

    Show only groups that contain the specified user.

  • group_principal

    Show only groups that contain the specified group.

  • regex_pattern

    A STRING literal with a limited regular expression pattern used to filter the results of the statement.

    • * at the start and end of a pattern matches on a substring.
    • * only at end of a pattern matches the start of a group.
    • | separates multiple regular expressions, any of which can match.
    • The pattern match is case-insensitive.

Examples

-- Lists all groups.
> SHOW GROUPS;
         name directGroup
 ------------ -----------
     tv_alien        NULL
        alien        NULL
  californian        NULL
  pastafarian        NULL

-- Lists groups with name containing with string pattern `rou`.
> SHOW GROUPS LIKE '*al*';
         name directGroup
 ------------ -----------
     tv_alien        NULL
        alien        NULL
  californian        NULL

-- Lists groups with Alf as a member.
> SHOW GROUPS WITH USER `alf@melmak.et`;
         name directGroup
 ------------ -----------
     tv_alien        true
        alien       false