FirebirdSQL logo

SHOW SQL DIALECT

SHOW SQL DIALECT

This command shows the dialect of the current database as well as the dialect of the current connection.

SQL> show SQL Dialect;
        Client SQL dialect is set to: 3 and database SQL dialect is: 3

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

Added in

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.