isql SHOW commands

<< isql SET commands | Firebird Interactive SQL Utility | A: Document history >>

`isql SHOW` commands

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.

`SHOW CHECKs`

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

`SHOW COLLATIONs`

 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.

`SHOW COMMENTs`

 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.

`SHOW DATABASE`

 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.

`SHOW DEPENdencies`

 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.

`SHOW DOMAINs`

 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

`SHOW EXCEPtions`

 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.

`SHOW FILTERs`

 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

`SHOW FUNCtions`

 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

`SHOW GENERATORs`

 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

`SHOW GRANTs`

 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.

`SHOW INDexes`

 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)

`SHOW PROCedures`

 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)

`SHOW ROLEs`

 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

`SHOW SECCLAsses`

 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

`SHOW SECURITY CLAsses`

 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

`SHOW SEQuences`

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.

`SHOW SQL DIALECT`

 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

`SHOW SYStem`

 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.

`SHOW TABLEs`

 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.

`SHOW TRIGgers`

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

`SHOW VERsion`

 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.

`SHOW USERS`

 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

`SHOW VIEWs`

 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