# Show command

The `Show` command displays information about active connections and database objects.

## Syntax

    SHOW
    {
       CONNECTIONS |
       FUNCTIONS [ IN schemaName ] |
       INDEXES [ IN schemaName | FROM tableName ] |
       PROCEDURES [ IN schemaName ] |
       ROLES |
       ENABLED_ROLES |
       SETTABLE_ROLES |
       SCHEMAS |
       SYNONYMS [ IN schemaName ] |
       TABLES [ IN schemaName ] |
       VIEWS [ IN schemaName ] |

    }

## SHOW CONNECTIONS

If there are no connections, the `SHOW CONNECTIONS` command returns "No connections available".

Otherwise, the command displays a list of connection names and the URLs used to connect to them. The currently active connection, if there is one, is marked with an asterisk (`*`) after its name.

## SHOW CONNECTIONS Example

    ij> connect 'sample' as sample1;
    ij> connect 'newDB;create=true' as newDB;
    ij(NEWDB)> show connections;
    SAMPLE1 -        jdbc:derby:sample
    NEWDB* -        jdbc:derby:newDB;create=true
    * = current connection
    ij(NEWDB)>

## SHOW FUNCTIONS

The `SHOW FUNCTIONS` command displays all functions in the database. By default, both system functions and user-defined functions appear in the output.

If `IN schemaName` is specified, only the functions in the specified schema are displayed.

## SHOW FUNCTIONS Example

If you created the `TO_DEGREES` function described in "CREATE FUNCTION statement" in the Derby Reference Manual, the output of the CREATE FUNCTION statement and the `SHOW FUNCTIONS` command would look something like the following:

    ij> connect 'jdbc:derby:firstdb';
    ij> CREATE FUNCTION TO_DEGREES ( RADIANS DOUBLE )
    > RETURNS DOUBLE
    > PARAMETER STYLE JAVA
    > NO SQL LANGUAGE JAVA
    > EXTERNAL NAME 'java.lang.Math.toDegrees';
    0 rows inserted/updated/deleted
    ij> show functions in app;
    FUNCTION_SCHEM|FUNCTION_NAME               |REMARKS
    -------------------------------------------------------------------------
    APP           |TO_DEGREES                  |java.lang.Math.toDegrees

    1 row selected

## SHOW INDEXES

The `SHOW INDEXES` command displays all the indexes in the database.

If `IN schemaName` is specified, only the indexes in the specified schema are displayed.

If `FROM tableName` is specified, only the indexes on the specified table are displayed.

## SHOW INDEXES Examples

    ij> show indexes in app;
    TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
    ----------------------------------------------------------------------------
    AIRLINES            |AIRLINE             |false |3   |A   |NULL    |NULL
    COUNTRIES           |COUNTRY_ISO_CODE    |false |3   |A   |NULL    |NULL
    COUNTRIES           |COUNTRY             |false |3   |A   |NULL    |NULL
    CITIES              |CITY_ID             |false |3   |A   |NULL    |NULL
    FLIGHTS             |FLIGHT_ID           |false |3   |A   |NULL    |NULL
    FLIGHTS             |SEGMENT_NUMBER      |false |3   |A   |NULL    |NULL
    FLIGHTAVAILABILITY  |FLIGHT_ID           |false |3   |A   |NULL    |NULL
    FLIGHTAVAILABILITY  |SEGMENT_NUMBER      |false |3   |A   |NULL    |NULL
    FLIGHTAVAILABILITY  |FLIGHT_DATE         |false |3   |A   |NULL    |NULL
    MAPS                |MAP_ID              |false |3   |A   |NULL    |NULL
    MAPS                |MAP_NAME            |false |3   |A   |NULL    |NULL
    FLIGHTS             |DEST_AIRPORT        |true  |3   |A   |NULL    |NULL
    FLIGHTS             |ORIG_AIRPORT        |true  |3   |A   |NULL    |NULL
    CITIES              |COUNTRY_ISO_CODE    |true  |3   |A   |NULL    |NULL
    FLIGHTAVAILABILITY  |FLIGHT_ID           |true  |3   |A   |NULL    |NULL
    FLIGHTAVAILABILITY  |SEGMENT_NUMBER      |true  |3   |A   |NULL    |NULL

    16 rows selected
    ij> show indexes from flights;
    TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
    ----------------------------------------------------------------------------
    FLIGHTS             |FLIGHT_ID           |false |3   |A   |NULL    |NULL
    FLIGHTS             |SEGMENT_NUMBER      |false |3   |A   |NULL    |NULL
    FLIGHTS             |DEST_AIRPORT        |true  |3   |A   |NULL    |NULL
    FLIGHTS             |ORIG_AIRPORT        |true  |3   |A   |NULL    |NULL

    4 rows selected

## SHOW PROCEDURES

The `SHOW PROCEDURES` command displays all the procedures in the database that have been created with the CREATE PROCEDURE statement, as well as system procedures.

If `IN schemaName` is specified, only procedures in the specified schema are displayed.

## SHOW PROCEDURES Example

    ij> show procedures in syscs_util;
    PROCEDURE_SCHEM     |PROCEDURE_NAME                |REMARKS
    ------------------------------------------------------------------------
    SYSCS_UTIL          |SYSCS_BACKUP_DATABASE         |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_BACKUP_DATABASE_AND_ENA&|org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_BACKUP_DATABASE_AND_ENA&|org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_BACKUP_DATABASE_NOWAIT  |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_BULK_INSERT             |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_CHECKPOINT_DATABASE     |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_COMPRESS_TABLE          |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_DISABLE_LOG_ARCHIVE_MODE|org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_EXPORT_QUERY            |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_EXPORT_TABLE            |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_FREEZE_DATABASE         |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_IMPORT_DATA             |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_IMPORT_TABLE            |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_INPLACE_COMPRESS_TABLE  |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_SET_DATABASE_PROPERTY   |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_SET_RUNTIMESTATISTICS   |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_SET_STATISTICS_TIMING   |org.apache.derby.ca&
    SYSCS_UTIL          |SYSCS_UNFREEZE_DATABASE       |org.apache.derby.ca&

    18 rows selected

## SHOW ROLES, SHOW ENABLED_ROLES, and SHOW SETTABLE_ROLES

The `SHOW ROLES` command displays the names of all roles created, whether settable for the current session or not.

The `SHOW ENABLED_ROLES` command displays the names of all the roles whose privileges are available for the current session. That is, it shows the current role and any role contained in the current role. (For a definition of role containment, see "Using SQL roles" in the Derby Security Guide.)

The `SHOW SETTABLE_ROLES` command displays all the roles that the current session can set, that is, all roles that have been granted to the current user or to PUBLIC.

The roles shown by these commands are sorted in ascending order.

## SHOW ROLES, SHOW ENABLED_ROLES, and SHOW SETTABLE_ROLES Examples

In the following examples, both CASUALUSER and POWERUSER contain ANYUSER, but ANYUSER is not settable directly.

    ij> show roles;
    ROLEID
    ------------------------------
    ANYUSER
    CASUALUSER
    POWERUSER

    3 rows selected
    ij> show enabled_roles;
    ROLEID
    ------------------------------
    ANYUSER
    CASUALUSER

    2 rows selected
    ij> show settable_roles;
    ROLEID
    ------------------------------
    CASUALUSER
    POWERUSER

    2 rows selected

## SHOW SCHEMAS

The `SHOW SCHEMAS` command displays all of the schemas in the current connection.

## SHOW SCHEMAS Example

    ij> show schemas;
    TABLE_SCHEM
    ------------------------------
    APP
    NULLID
    SQLJ
    SYS
    SYSCAT
    SYSCS_DIAG
    SYSCS_UTIL
    SYSFUN
    SYSIBM
    SYSPROC
    SYSSTAT

    11 rows selected

## SHOW SYNONYMS

The `SHOW SYNONYMS` command displays all the synonyms in the database that have been created with the CREATE SYNONYMS statement.

If `IN schemaName` is specified, only synonyms in the specified schema are displayed.

## SHOW SYNONYMS Example

    ij> show synonyms;
    TABLE_SCHEM         |TABLE_NAME                    |REMARKS
    ------------------------------------------------------------------------
    APP                 |MYAIRLINES                    |

## SHOW TABLES

The `SHOW TABLES` command displays all of the tables in the current schema.

If `IN schemaName` is specified, the tables in the given schema are displayed.

## SHOW TABLES Example

    ij> show tables;
    TABLE_SCHEM         |TABLE_NAME                    |REMARKS
    ------------------------------------------------------------------------
    APP                 |AIRLINES                      |
    APP                 |CITIES                        |
    APP                 |COUNTRIES                     |
    APP                 |FLIGHTAVAILABILITY            |
    APP                 |FLIGHTS                       |
    APP                 |FLIGHTS_HISTORY               |
    APP                 |MAPS                          |

    7 rows selected

## SHOW VIEWS

The `SHOW VIEWS` command displays all of the views in the current schema.

If `IN schemaName` is specified, the views in the given schema are displayed.

## SHOW VIEWS Example

    ij> show views;
    TABLE_SCHEM         |TABLE_NAME                    |REMARKS
    ------------------------------------------------------------------------
    APP                 |TOTALSEATS                    |

    1 row selected

Parent topic: ij commands and errors reference
Related concepts
ij commands
ij errors
Related reference
Absolute command
After Last command
Async command
Autocommit command
Before First command
Close command
Commit command
Connect command
Describe command
Disconnect command
Driver command
Elapsedtime command
Execute command
Exit command
First command
Get Cursor command
Get Scroll Insensitive Cursor command
Help command
HoldForConnection command
Last command
LocalizedDisplay command
MaximumDisplayWidth command
Next command
NoHoldForConnection command
Prepare command
Previous command
Protocol command
Readonly command
Relative command
Remove command
Rollback command
Run command
Set Connection command
Wait For command
Syntax for comments in ij commands
Syntax for identifiers in ij commands
Syntax for strings in ij commands