SHOW GROUPS
Applies to: 
Databricks SQL 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.
To run this command you must be an administrator.
Syntax
SHOW GROUPS [ WITH USER user_principal |
WITH GROUP group_principal ]
[ [ LIKE ] regex_pattern ]
Parameters
-
Show only groups that contain the specified user.
-
Show only groups that contain the specified group.
-
regex_pattern
A
STRINGliteral 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.
RETURNS
A table with the following columns:
-
name STRING NOT NULLThe name of the group.
-
directGroup BOOLEANTRUE: The principal is a direct member of the group.FALSE: The principal is an indirect member of the group.NULL: If no principal was specified.
Examples
SQL
-- 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