Syntax

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

}

Description

The SHOW command can be used to display information about active connections and database objects.

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 * after its name.

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

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

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

Example

If you created the TO_DEGREES function described in "CREATE FUNCTION statement" in the Derby Reference Manual, the output of the CREATE FUNCTION and SHOW FUNCTIONS commands 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

SHOW INDEXES displays all the indexes in the database.

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

If FROM table-Name is specified, then only the indexes on the specified table are displayed.

Example

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

SHOW PROCEDURES 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.

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, SHOW SETTABLE_ROLES

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

SHOW ENABLED_ROLES 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 Developer's Guide.)

SHOW SETTABLE_ROLES 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.

Example

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

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

SHOW SCHEMAS

SHOW SCHEMAS displays all of the schemas in the current connection.

Example

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

11 rows selected

SHOW SYNONYMS

SHOW SYNONYMS 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.

Example

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

SHOW TABLES

SHOW TABLES displays all of the tables in the current schema.

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

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

SHOW VIEWS displays all of the views in the current schema.

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

Example

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

1 row selected