SHOW SYStem
SHOW SYStem [<object_type>] <object_type> :: = { TABLEs | COLLATIONs | COLLATEs | ROLEs | FUNCtions | PROCedures | PACKages | PUBLications }
This command lists the internal, i.e. system, objects created and used in the current database.The optional parameter restricts the listing to show only the specified object type.This applies from Firebird 2.0 onwards.Prior to version 2.0, the command would only list the system tables — equivalent to the show system tables
command.
If no parameter is passed, the listing will display tables, functions (not built-in functions though), collations, roles, procedures, packages, and publications.
Types PROCedures
, PACKages
and PUBLications
are available since Firebird 5.0.
SQL> show system; Tables: MON$ATTACHMENTS ... Functions: RDB$BLOB_UTIL.IS_WRITABLE ... Procedures: RDB$BLOB_UTIL.CANCEL_BLOB ... Packages: RDB$BLOB_UTIL ... Collations: ASCII ... Roles: RDB$ADMIN Publications: RDB$DEFAULT
Note that packaged procedures and functions are listed as <package-name>.<routine-name>
.
The show system <object_type>
can sometimes show more details compared to just show system
:
SQL> show system collations; ASCII, CHARACTER SET ASCII, PAD SPACE, SYSTEM ... WIN_PTBR, CHARACTER SET WIN1252, PAD SPACE, CASE INSENSITIVE, ACCENT INSENSITIVE, SYSTEM
If you wish to drill down and display details of a specific object, use the corresponding show <object-type> name
command, without system
.
SQL> show table mon$io_stats; MON$STAT_ID (RDB$STAT_ID) INTEGER Nullable MON$STAT_GROUP (RDB$STAT_GROUP) SMALLINT Nullable MON$PAGE_READS (RDB$COUNTER) BIGINT Nullable MON$PAGE_WRITES (RDB$COUNTER) BIGINT Nullable MON$PAGE_FETCHES (RDB$COUNTER) BIGINT Nullable MON$PAGE_MARKS (RDB$COUNTER) BIGINT Nullable SQL> show collation ascii; ASCII, CHARACTER SET ASCII, PAD SPACE, SYSTEM
SHOW SCHEmas
Firebird 3.0
SHOW SCHEmas
This command always returns an error.
SQL> show schemas; Command error: show schemas
SHOW TABLEs
SHOW TABLEs [name]
This command lists all user-defined tables in the database if the first form of the command is used, or displays the columns and data types or domains of the table if the second form is used.
SQL> show tables; COUNTRY CUSTOMER ... SQL> show table country; COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null CURRENCY VARCHAR(10) Not Null CONSTRAINT INTEG_2: Primary key (COUNTRY)
You will note that if there are comments defined for a table, this command will not display them.You must use the [isql-show-comments] command, but be aware that you will then be given all comments in the database.There is no command to extract the comments for a single object, unless you query the system tables directly.
SQL> comment on table country is 'This table holds details about countries.'; SQL> commit; SQL> show comments; ... COMMENT ON TABLE COUNTRY IS This table holds details about countries.; ... SQL> show table country; COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null CURRENCY VARCHAR(10) Not Null CONSTRAINT INTEG_2: Primary key (COUNTRY) SQL> select rdb$description CON> from rdb$relations CON> where rdb$relation_name = 'COUNTRY'; RDB$DESCRIPTION ================= 6:1e7 ============================================================================== RDB$DESCRIPTION: This is a table holding details about countries. ==============================================================================
The output from the final query above is not ideal, but at least it’s much less displayed information when there are lots of comments in your database.