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 for USERS, ROLES and TABLES. Else, it is just ignored.
  • ALL is not compatible with SQL output mode.
PERM

displays permissions for USERS, ROLES and TABLES only. Else, it is just ignored.

  • When used with SQL output mode, GRANT and DENY permissions statements are only displayed for TABLES, not for USERS nor ROLES:

          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
SHOW R
displays the list of all roles in the current database.
  • Use the PERM specifier to display permissions
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 to SHOW 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.

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
RSQL, a simple alternative to Microsoft SQL Server