SHOW GROUPS (Databricks SQL)

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 | GROUP group} ] | [ LIKE regex_pattern ] }

Parameters

  • user

    Show only groups that contain the specified user.

  • group

    Show only groups that contain the specified group.

  • regex_pattern

    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
 ------------ ------------
       group1         NULL
       group2         NULL
  collection1         NULL

-- Lists groups with name containing with string pattern `rou`.
> SHOW GROUPS LIKE '*rou*';
         name  directGroup
 ------------ ------------
       group1         NULL
       group2         NULL

-- Lists groups with user1 as a member.
> SHOW GROUPS WITH USER `user1@example.com`;
         name  directGroup
 ------------ ------------
       group1         true
       group2        false