As explained in the help command, there are a number of individual SHOW commands within isql. The general format of the SHOW commands is:

SQL> SHOW <object> [name] ;

The object is always required and the name is required to display details of a specific object. Without a name, the commands will normally display all the objects of the requested type. Unfortunately, unlike the SET commands, there is no handy drill down into the various SHOW commands using the HELP command. However, if you type SHOW on its own, you will be given a little more assistance.

SQL> show;

Valid options are:
CHECKs               COMMENTs                COLLATEs
COLLATIONs           DOMAINs                 DB
DATABASE             DEPENdency              DEPENdencies
EXCEPtions           FILTERs                 FUNCtions
GENerators           GRANTs                  INDexes
INDICES              PROCedures              ROLEs
SYStem               SEQuences               SECURITY CLAsses
SECCLAsses           TABLEs                  TRIGgers
USERS                VIEWs
Command error: show

The upper case letters indicate what you must type as an absolute minimum.

The SHOW commands are detailed and described below. Where possible, examples from the employee database are shown.

SQL> SHOW CHECKs table_name;

This command displays all user-defined check constraints defined for a specific table. Unlike other SHOW commands, there is no option to display a list of all the check constraints in the database. You must always provide a table name as part of the command.

SQL> show check employee;

CONSTRAINT INTEG_30:
  CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
          salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country))

SQL> SHOW {COLLATIONs | COLLATION name};
SQL> SHOW {COLLATEs | COLLATE name};

These commands display a list of all the user-defined collations in the current database. It is only available from Firebird 2.0 onwards. The first form of the commands display a list of all the collations while a specific collation may be displayed by providing the collation name.

SQL> show collations;
UNICODE_ENUS_CI, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
CASE INSENSITIVE, 'COLL-VERSION=58.0.6.48'
UNICODE_ENUS_CS, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
'COLL-VERSION=58.0.6.48'

SQL> show collation unicode_enus_ci;
UNICODE_ENUS_CI, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
CASE INSENSITIVE, 'COLL-VERSION=58.0.6.48'

You can see from the output above, which is not part of the employee database, does appear to display all the relevant information in the first form of the command. There does not appear to be much reason to drill down into a specific collation - at least, not according to this example. Some lines in the above have had to be split over two to allow it to fit on the page.

SQL> SHOW COMMENTs;

This command displays all comments that have been created, on various objects, in the current database. There is no option to display a specific comment. Each comment is listed along with the object type and name, to which it has been applied.

SQL> show comments;

COMMENT ON DATABASE IS This is the demonstration EMPLOYEE database.;
COMMENT ON TABLE EMPLOYEE IS The EMPLOYEE table has details of our employees.;

The actual comment text is shown between the word IS and the trailing semicolon.

SQL> SHOW DATABASE;
SQL> SHOW DB;

The SHOW DATABASE (or SHOW DB) command displays details about the current database. The ODS version, shown in the following examples, is only displayed from Firebird version 2.0 onwards.

SQL> show database; 

Database: employee
        Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 270
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 190
Transaction - oldest active = 191
Transaction - oldest snapshot = 191
Transaction - Next = 211
ODS = 11.2
Default Character set: NONE

No parameters, such as a specific database name, are required and if supplied, will be ignored. The details displayed will always be for the current database.

SQL> show database testing_db;

Database: employee
        Owner: SYSDBA
PAGE_SIZE 4096
...
Default Character set: NONE

You will note from the above that the details displayed are still for the employee database.

SQL> SHOW DEPENdencies object_name;
SQL> SHOW DEPENdency object_name;

These commands display all dependencies for the specified object name supplied as a parameter. The object name supplied need not necessarily be a table name, it could be a function or procedure name, a sequence name etc.

The output listed is a comma separated list of the other objects in the database upon which the supplied object is dependent. In other words, a procedure would fail to compile if any of the listed dependencies was to be removed, for example.

SQL> show dependencies SET_CUST_NO; 

        [SET_CUST_NO:Trigger]
CUSTOMER:Table<-CUST_NO, CUST_NO_GEN:Generator
+++

The listing above shows that SET_CUST_NO is a trigger and that it is dependent on two separate objects, the CUST_NO column of table CUSTOMER and the sequence/generator named CUST_NO_GEN. If you display the trigger itself, you will see both of those objects mentioned:

SQL> show trigger set_cust_no;

Triggers on Table CUSTOMER:
SET_CUST_NO, Sequence: 0, Type: BEFORE INSERT, Active
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Sometimes, the output can be a little confusing. You may see various objects in the list that don't appear to be relevant. The RDB$DEPENDENCIES table, where the data comes from, also holds details of system objects upon which a given object will depend.

SQL> SHOW {DOMAINs | DOMAIN name};

This command displays domain information. A domain is a user-defined data type, global to the database. It is used to define the format and range of columns, upon which the actual column definitions in tables are based.

Firebird tables are defined by the specification of columns, which store appropriate information in each column using data types.

A data type is an elemental unit when defining data, which specifies the type of data stored in tables, and which operations may be performed on this data. It can also include permissible calculative operations and maximum data size. Examples of data types include: numerical (numeric, decimal, integer);textual (char, varchar, nchar, nvarchar); date (date, time, timestamp) and blobs(binary large objects).

As with many show commands, there are two forms. The first displays a list of all known domains in the database while the second allows you to display the details of a specific domain.

SQL> show domain;

       ADDRESSLINE         BUDGET
       COUNTRYNAME         CUSTNO
       DEPTNO              EMPNO
       FIRSTNAME           JOBCODE
...

SQL> show domain addressline;
ADDRESSLINE           VARCHAR(30) Nullable

SQL> SHOW {EXCEPtions | EXCEPtion name};

This command displays all the exceptions which have been defined in the current database. Details of the exception's error message and objects which use the exception - those which are dependant upon the exception - are also shown. You may display individual exception's details with the second FOR of the command.

SQL> show exceptions;

Exception Name                  Used by, Type
=============================== =============================================
CUSTOMER_CHECK                  SHIP_ORDER, Stored procedure
Msg: Overdue balance -- can not ship.

CUSTOMER_ON_HOLD                SHIP_ORDER, Stored procedure
Msg: This customer is on hold.

... 

SQL show exception customer_on_hold;

Exception Name                  Used by, Type
=============================== =============================================
CUSTOMER_ON_HOLD                SHIP_ORDER, Stored procedure
Msg: This customer is on hold.

SQL> SHOW {FILTERs | FILTER name};

This command displays a list of all known BLOB filters declared in the current database using the DECLARE FILTER command. The second form of the command allows the full details of a specific filter to be displayed.

SQL> show filter;

       FUNNEL
       ...

SQL> show filter funnel;
BLOB Filter: FUNNEL
        Input subtype: 2 Output subtype: 1
        Filter library is myfilterlib
        Entry point is blr2asc

SQL> SHOW {FUNCtions | FUNCtion name};

This command allows a list of all external functions declared in the current database, to be displayed. External functions are those defined and coded in various UDF libraries.

The second form of the command allows the details of a specific function to be displayed.

SQL> show functions;

      ADDDAY           ADDDAY2
      ADDHOUR          ADDMILLISECOND
      ADDMINUTE        ADDMONTH
      ADDSECOND        ADDWEEK
      ADDYEAR

SQL> show function addyear;

Function ADDYEAR:
Function library is fbudf
Entry point is addYear
Returns TIMESTAMP
Argument 1: TIMESTAMP
Argument 2: INTEGER

SQL> SHOW {GENERATORs | DOMAIN name};
SQL> SHOW {SEQuences | SEQuence name};

These two commands are identical. Generators was the old Firebird term for what are more commonly known as sequences in other databases, as well as the ANSII Standards. You are encouraged to use sequences rather than generators but isql considers them to be the same.

The first form of the commands above list all the sequences in the current database, while the second form displays details of a specific sequence.

SQL> show sequences;

Generator CUST_NO_GEN, current value is 1015
Generator EMP_NO_GEN, current value is 145

SQL> show sequence emp_no_gen;

Generator EMP_NO_GEN, current value is 145

SQL> SHOW {GRANTs | GRANT {object_name | role_name}};

This command displays a list of all grants in the current database if the first format of the command is used. The second drills down and displays only those details for the selected object, which may be a table, procedure, etc. Alternatively, if a role name is provided, only a list of users who have been granted that role will be displayed.

SQL> show grants;

/* Grant permissions for this database */
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
ON COUNTRY TO PUBLIC WITH GRANT OPTION
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
ON CUSTOMER TO PUBLIC WITH GRANT OPTION
...
GRANT SELECT ON EMPLOYEE TO ROLE DEFAULT_USER
...
GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ TO PUBLIC WITH GRANT OPTION
GRANT EXECUTE ON PROCEDURE ALL_LANGS TO PUBLIC WITH GRANT OPTION
...

SQL> show grants employee; 

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
ON EMPLOYEE TO PUBLIC WITH GRANT OPTION

SQL> show grants ship_order;

GRANT EXECUTE ON PROCEDURE SHIP_ORDER TO PUBLIC WITH GRANT OPTION

SQL> show grants default_user;

GRANT DEFAULT_USER TO SYSDBA

Note that some lines in the above have been split to allow them to fit on the page.

SQL> SHOW {INDexes | INDICES}; 
SQL> SHOW {INDICES | INDexes} table_name;
SQL> SHOW INDex index_name;

The first form of this command will list all the indexes in the current database. In this form, you may specify either indexes or indices, they are treated as identical by Firebird. The second form of the command will display the list of indices for a specific table as determined by the table_name parameter. Again, you may specify indexes or indices. The final form of the command displays details of a given index and in this form of the command, you must use the keyword index.

SQL> show indices;

RDB$PRIMARY1 UNIQUE INDEX ON COUNTRY(COUNTRY)
CUSTNAMEX INDEX ON CUSTOMER(CUSTOMER)
...
SALESTATX INDEX ON SALES(ORDER_STATUS, PAID)

SQL> show indices employee;

NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)
RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO)
RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY)
RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO)

SQL> show index namex;

NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)

SQL> SHOW {PROCedures | PROCedure name};

This command allows a list of all procedures created in the current database, to be displayed. The second form of the command allows the details and source code to be shown for a specific procedure. See also the SHOW FUNCTIONs and SHOW TRIGGERs commands.

SQL> show procedures;

Procedure Name                    Invalid Dependency, Type
================================= ======= =====================================
ADD_EMP_PROJ                              EMPLOYEE_PROJECT, Table
                                          UNKNOWN_EMP_ID, Exception
ALL_LANGS                                 JOB, Table
                                          SHOW_LANGS, Procedure
...

SQL> show procedure all_langs;
Procedure text:
=============================================================================
   BEGIN
       FOR SELECT job_code, job_grade, job_country FROM job
               INTO :code, :grade, :country
       DO
       BEGIN
           FOR SELECT languages FROM show_langs
                   (:code, :grade, :country) INTO :lang DO
               SUSPEND;
           /* Put nice separators between rows */
           code = '=====';
           grade = '=====';
           country = '===============';
           lang = '==============';
           SUSPEND;
       END
   	END
=============================================================================
Parameters:
CODE                            OUTPUT VARCHAR(5)
GRADE                           OUTPUT VARCHAR(5)
COUNTRY                         OUTPUT VARCHAR(15)
LANG                            OUTPUT VARCHAR(15)

SQL> SHOW {ROLEs | ROLE name};

This command lists all the roles in the current database if the first form is used or, drills down to display a list of all the users who have been granted a specific role if the second form of the command is used.

SQL> show roles;

       DEFAULT_USER

SQL> show role default_user; 

Role DEFAULT_USER is granted to:

SYSDBA

SQL> SHOW SECCLAsses object_name;

This command displays details about the security classes for a given object. The object_name passed to the command need not be a table name, the command works for tables, procedures etc.

SQL> show secclasses employee;

Table's main sec class SQL$7
Table's default sec class SQL$DEFAULT7

SQL> SHOW SECURITY CLAsses name;

This command always returns an error.

SQL> show security classes;
Command error: show security classes

SQL> show security classes employee;
Command error: show security_classes employee

The Firebird specific name, generator, has been updated to match the ANSII standard term sequence. The SHOW SEQuences command is identical to SHOW GENERATORs (above) and the output is identical.

SQL> SHOW SQL DIALECT;

This command, which must be entered in full, shows the current database's dialect as well as the dialect used by the currently connected client.

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

SQL> SHOW SYStem [tables];

This command lists the internal, i.e. system, objects created and used in the current database. The optional parameter - tables - restricts the listing to show only tables. 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 (internal as opposed to external ones) and collations.

SQL> show system;
Tables:
       MON$ATTACHMENTS           MON$CALL_STACK
...
       RDB$USER_PRIVILEGES       RDB$VIEW_RELATIONS

Functions:
       RDB$GET_CONTEXT           RDB$SET_CONTEXT 

Collations:
       ASCII                     BIG_5
...
       WIN1258                   WIN_CZ
       WIN_CZ_CI_AI              WIN_PTBR

If you wish to drill down and display details of a specific object, simply use the corresponding command.

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 function rdb$get_context;

Function RDB$GET_CONTEXT:
Function library is system_module
Entry point is get_context
Returns FREE_IT VARCHAR(255) CHARACTER SET NONE
Argument 1: NULL VARCHAR(80) CHARACTER SET NONE
Argument 2: NULL VARCHAR(80) CHARACTER SET NONE

SQL> show collation ascii;

ASCII, CHARACTER SET ASCII, PAD SPACE, SYSTEM

You will note that the SHOW FUNCtions command will display details of internal functions as well as those defined externally.

SQL> SHOW {TABLEs | TABLE name};

This command lists the user-defined tables in the database if the first form of the command is used, or displays the columns and data types or domains making up the table if the second form is used, with a table name supplied as a parameter.

SQL> show tables;

       COUNTRY                CUSTOMER
       DEPARTMENT             EMPLOYEE
       EMPLOYEE_PROJECT       JOB
       PROJECT                PROJ_DEPT_BUDGET
       SALARY_HISTORY         SALES

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 SHOW COMMENTs command but be aware that you will then be given all comments in the database. There doesn't appear to be a method of extracting 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 displays much less information if there are lots of comments in your database.

SQL> SHOW {TRIGgers | TRIGger name};

This command allows a list of all triggers created in the current database to be displayed. The second form of the command allows the details and source code to be shown for a specific trigger. See also the SHOW PROCedures and SHOW FUNCtions commands.

SQL> show triggers;

Table name                       Trigger name                     Invalid
================================ ================================ =======
CUSTOMER                         SET_CUST_NO
EMPLOYEE                         SAVE_SALARY_CHANGE
EMPLOYEE                         SET_EMP_NO
SALES                            POST_NEW_ORDER

SQL> show trigger set_cust_no;

Triggers on Table CUSTOMER:
SET_CUST_NO, Sequence: 0, Type: BEFORE INSERT, Active
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> SHOW VERsion;

This command displays details about the Firebird software, your database and the on disk structure (ODS) in use.

SQL> show version;

ISQL Version: LI-V2.5.1.26351 Firebird 2.5
Server version:
Firebird/linux AMD64 (access method),
version "LI-V2.5.1.26351 Firebird 2.5"
Firebird/linux AMD64 (remote server),
version "LI-V2.5.1.26351 Firebird 2.5/tcp (hubble)/P12"
Firebird/linux AMD64 (remote interface),
version "LI-V2.5.1.26351 Firebird 2.5/tcp (hubble)/P12"
on disk structure version 11.2

The above output has been adjusted to fit on the page. Each pair of lines beginning with Firebird and version are normally displayed as a single line. They are split over two lines here.

SQL> SHOW USERS;

This command shows a list of users who are currently connected to the database. If a user is logged in on more than one session, all sessions will be displayed separately.

SQL> show users;

Users in the database
# SYSDBA                # SYSDBA
# NORMAN

SQL> SHOW {VIEWs | VIEW name};

The first form of this command displays a list of all views in the current database. Drilling down using the second form of the command will display the columns and source code for a specific view.

SQL> show views;

       PHONE_LIST

SQL> show view phone_list;

EMP_NO                     (EMPNO) SMALLINT Not Null
FIRST_NAME                 (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME                  (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT                  VARCHAR(4) Nullable
LOCATION                   VARCHAR(15) Nullable
PHONE_NO                   (PHONENUMBER) VARCHAR(20) Nullable
View Source:
==== ======
  SELECT
     emp_no, first_name, last_name, phone_ext, location, phone_no
     FROM employee, department
     WHERE employee.dept_no = department.dept_no