Show command The Show command displays information about active connections and database objects. Show command Show Tables command Show Connections command Multiple connectionsdisplaying Show Schemas command Show Views command Show Procedures command Show Synonyms command Show Indexes in Schema command Show Indexes from Table command Rolesdisplaying SHOW ROLES command SHOW ENABLED_ROLES command SHOW SETTABLE_ROLES command SHOW FUNCTIONS command 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 , 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 .)

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