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 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