Firebird Documentation IndexIsql - Interactive SQL → Isql Show commands
Firebird Home Firebird Home Prev: Isql Set CommandsFirebird Documentation IndexUp: Isql - Interactive SQLNext: Document History

Isql Show commands

Show Checks
Show Collations
Show Comments
Show Database
Show Dependencies
Show Domains
Show Exceptions
Show Filters
Show Functions
Show Generators
Show Grants
Show Indexes
Show Procedures
Show Roles
Show Secclasses
Show Security Classes
Show Sequences
Show SQL Dialect
Show System
Show Tables
Show Triggers
Show Version
Show Users
Show Views

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 indocate 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 comments is listed along with the object type and name, to whihc 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 enetered 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, ie 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 show 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 function 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's much less displayed information when 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 disc 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
Prev: Isql Set CommandsFirebird Documentation IndexUp: Isql - Interactive SQLNext: Document History
Firebird Documentation IndexIsql - Interactive SQL → Isql Show commands