SHOW (RSQL Command)
displays the list of logins, databases, users, roles, tables, etc.
This is the main command to list the objects in RSQL Server.
Syntax
-- shows list of supported languages --
SHOW { LANG|LANGUAGE|LANGUAGES }
-- shows list of supported collations --
SHOW { COLL|COLLATION|COLLATIONS }
-- shows all workers (sessions) --
SHOW { WORKER|WORKERS }
-- shows all current locks on databases and tables by any session --
SHOW { LOCK|LOCKS }
-- shows information about current session --
SHOW { I|INFO }
-- shows server parameters --
SHOW [<output_mode>] {P|PARAM|PARAMS|PARAMETER|PARAMETERS} [parameter_name]
SHOW [<output_mode>] {P|PARAM|PARAMS|PARAMETER|PARAMETERS} [LIKE] 'pattern'
-- shows logins --
SHOW [<output_mode>] {L|LOGIN|LOGINS} [login_name]
SHOW [<output_mode>] {L|LOGIN|LOGINS} [LIKE] 'pattern'
-- shows databases --
SHOW [<output_mode>] {D|DB|DBS|DATABASE|DATABASES} [database_name]
SHOW [<output_mode>] {D|DB|DBS|DATABASE|DATABASES} [LIKE] 'pattern'
-- shows users --
SHOW [ALL] [PERM] [<output_mode>] {U|USER|USERS} [user_name]
SHOW [ALL] [PERM] [<output_mode>] {U|USER|USERS} [LIKE] 'pattern'
-- shows roles --
SHOW [ALL] [PERM] [<output_mode>] {R|ROLE|ROLES} [role_name]
SHOW [ALL] [PERM] [<output_mode>] {R|ROLE|ROLES} [LIKE] 'pattern'
-- shows tables --
SHOW [ALL] [PERM] [<output_mode>] {T|TABLE|TABLES} [<table_qname>] [-]
SHOW [ALL] [PERM] [<output_mode>] {T|TABLE|TABLES} [LIKE] 'pattern'
<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name
Arguments
ALL
- objects in all databases are output. Without
ALL
, only objects in current database are output.ALL
is only available forUSERS
,ROLES
andTABLES
. Else, it is just ignored.ALL
is not compatible withSQL
output mode.
PERM
displays permissions for
USERS
,ROLES
andTABLES
only. Else, it is just ignored.When used with
SQL
output mode, GRANT and DENY permissions statements are only displayed forTABLES
, not forUSERS
norROLES
:SHOW SQL PERM TABLES
- <output_mode> ::=
NORMAL | ID | SQL | {TEMPLATE = 'template_text'}
specifies the output mode as plain list, SQL script, or customized output from template.
NORMAL
output is a plain list. It is the default output if nothing is specified.
ID
same as
NORMAL
, but also displays the id of the objects, used internally by RSQL.SQL
output is a SQL creation script for the objects.
TEMPLATE =
‘template_text’output is the template text, with placeholders replaced by proper values. Available placeholder names are:
for PARAMETERS: {{.parameter_name}} {{.parameter_id}} {{.parameter_value}}
for LOGINS: {{.login_name}} {{.login_id}} {{.default_database_name}}
{{.default_language}} {{.disabled}}
for DATABASES: {{.database_name}} {{.database_id}} {{.database_owner}}
{{.database_status}} {{.database_mode}} {{.database_access}}
for USERS: {{.user_name}} {{.user_id}} {{.database_name}}
{{.member_of}} {{.login_name}} {{.disabled}}
for ROLES: {{.role_name}} {{.role_id}} {{.database_name}}
{{.member_of}}
for TABLES: {{.database_name}} {{.schema_name}} {{.table_name}}
{{.table_id}}
See Golang Template package for more information about available template syntax:
https://golang.org/pkg/text/template/
In addition to the predefined global functions available in templates, e.g. "printf", "len", "eq", the function "join" is also available (see example below).
A template is very useful if you want to create statements to drop all users, tables, etc (see example below).
- parameter_name, login_name, database_name, user_name, role_name, <table_qname>
- is the name of the object. If omitted, all objects in current database will be listed.
- <table_qname>
- is the qualified or unqualified name of a table. You can specify only the first characters of a table name.
-
- a hyphen after a table name searches for an exact match.
- pattern
- is a string, with same syntax as
LIKE
.
Remarks
The command SHOW
displays the list of parameters, logins, databases, users, roles, and tables.
For USERS
, ROLES
and TABLES
, unless the ALL
specifier is used, only the objects in the current database are listed.
You can modify the current database with the statement USE
.
The following subcommands are available:
SHOW LANG
- displays all supported languages, that can be passed as argument to
SET LANGUAGE
. SHOW COLL
- displays all supported collations, that can be passed as argument to
COLLATE
clause. SHOW WORKER
- displays all workers. A worker is the process that executes a query inside a session on behalf of its client.
SHOW LOCK
- displays all locks currently held on databases and tables by any session.
SHOW I
- displays the server name, server default collation, current login, user, database, language.
SHOW P
- displays current server parameters. They can be modified by
ALTER SERVER PARAMETER
. SHOW L
- displays the list of all logins.
SHOW D
- displays the list of all databases.
SHOW U
- displays the list of all users and roles in the current database.
- Use the
PERM
specifier to display permissions
- Use the
SHOW R
- displays the list of all roles in the current database.
- Use the
PERM
specifier to display permissions
- Use the
SHOW T
- displays the list of all tables in the current database.
- Use the
PERM
specifier to display permissions - To display the columns, datatypes and indexes of tables, you must use the SQL output mode:
SHOW SQL T
As a particular case, if a table name is specified in full or only the first characters, the SQL script for the table is displayed instead of a list:SHOW T clients
is converted toSHOW SQL T clients
because most probably, you are interested in displaying the table layout when you pass a table name as argument.
SHOW ID T
displays the file path of the tables.- To display only the table matching the name exactly, append a hyphen
-
after the name.
SHOW T cust
displays tables cust, customers, customs_fees, etc.
SHOW T cust-
displays only table cust.
- Use the
Examples
SHOW P
SHOW PARAMETERS same as SHOW P
SHOW SQL P output is SQL statements
SHOW L
SHOW LOGINS same as SHOW L
SHOW D
SHOW DB same as SHOW D
SHOW DBS same as SHOW D
SHOW DATABASE same as SHOW D
SHOW DATABASES same as SHOW D
SHOW U
SHOW USER same as SHOW U
SHOW PERM U displays permissions on tables for each user and role
SHOW ALL PERM ID U same as above, with user and role IDs, for all databases
SHOW R
SHOW ROLE same as SHOW R
SHOW PERM R displays permissions on tables for each role
SHOW T
SHOW TABLE same as SHOW T
SHOW TABLES same as SHOW T
SHOW NORMAL TABLES same as SHOW T
SHOW ID TABLES displays the file path of the tables
SHOW PERM TABLES displays the permissions on the tables
SHOW TABLE clients displays CREATE TABLE statement for all tables which name starts by 'clients'
SHOW SQL TABLE clients same as above
SHOW TABLE store..clients
SHOW SQL PERM TABLE clients displays CREATE TABLE statement and GRANT/DENY permission statements
SHOW TABLE clients- displays CREATE TABLE statement for the 'clients' table
SHOW SQL T displays CREATE TABLE statement for all tables in the current database
SHOW TABLE LIKE 'cli%'
SHOW TABLE 'cli%' if argument is a literal string, LIKE is implied
SHOW ALL TABLE displays tables in all databases
SHOW ALL TABLE clients displays the tables [clients] found in all databases
SHOW ALL TABLE LIKE 'cli%'
-- to familiarize yourself with templates, study and run the following commands:
SHOW TEMPLATE = 'Hello, my name is {{.user_name}} and I live in database {{.database_name}}.' USERS
SHOW TEMPLATE = '{{.user_name}} is member of {{join .member_of ", "}}' USERS
SHOW TEMPLATE = 'drop user {{.user_name}};' USERS
SHOW TEMPLATE = '{{if eq .database_name "trashdb"}}TRASHDB is here{{else}}This is database {{.database_name}}{{end}}' DATABASE
SHOW TEMPLATE = '{{if eq .default_database_name "customers"}}ALTER LOGIN [{{.login_name}}] WITH DEFAULT_DATABASE=[clients];{{end}}' LOGINS