SHOW Commands to display metadata - Firebird
Interview Questions
All Tutorials
Job SearchSHOW commands are used to display metadata, including tables, indexes, procedures, triggers, and privileges. They can list the names of all objects of the specified type or supply detailed information about a particular object named in the command.
The SHOW commands are (approximately) the interactive equivalent of the command-line –extract, –x , or –a option (see “Extracting Metadata”). However, although you can use the OUTPUT command to send the output of the SHOW commands to a file, the saved text is not ready to use as a schema script without editing. Use the command- line options if obtaining a schema script is your goal.
Each SHOW command runs in its own READ COMMITTED statement, ensuring that each call returns the most up-to-date view of the state of the database.
SHOW CHECK
displays the names and sources for all user-defined CHECK constraints defined for a specified table.
SQL> SHOW CHECK tablename ; Tablename Name of a table that exists in the attached databaseExample:
...SQL> SHOW CHECK JOB ;
CONSTRAINT INTEG_12
CHECK (min_salary < max_salary)
SHOW DATABASE displays information about the attached database (file name, page size and allocation, sweep interval, transaction numbers, Forced Writes status, default character set). SHOW DB is a shorthand version of the command.
SQL> SHOW DATABASE | DB ;SHOW DATABASE takes no arguments. Figure shows the output you can expect from SHOW DATABASE.
SHOW DATABASE output
For version and on-disk structure information, use SHOW VERSION.
SHOW DOMAIN[S]
displays domain information.
SQL> SHOW { DOMAINS | DOMAIN name };![SHOW DOMAIN[S]](/tutorials/show-domain-s.png "SHOW DOMAIN[S]")
Examples:
SQL> SHOW DOMAINS ;D_CURRENCY D_NOTES
D_BOOLEAN D_PHONEFAX
... ...
SQL> SHOW DOMAIN D_BOOLEAN ;
D_BOOLEAN SMALLINT NOT NULL
DEFAULT 0
CHECK (VALUE IN (0,1)
SHOW EXCEPTION[S] displays exception information.
SQL> SHOW { EXCEPTIONS | EXCEPTION name };![SHOW EXCEPTION[S]](/tutorials/show-exception-s.png "SHOW EXCEPTION[S]")
Examples:
...SQL> SHOW EXCEPTIONS ;
Exception Name Used by, Type
=============== ============================
BAD_WIZ_TYPE UPD_FAVEFOOD, Stored procedure
Invalid Wiz type, check CAPS LOCK
...
SQL> SHOW EXCEPTION BAD_WIZ_TYPE ;
Exception Name Used by, Type
=============== ============================
BAD_WIZ_TYPE UPD_FAVEFOOD, Stored procedure
Invalid Wiz type, check CAPS LOCK
SHOW FUNCTION[S]
displays information about external functions declared in the attached database.
SQL> SHOW { FUNCTIONS | FUNCTION name };![SHOW FUNCTION[S]](/tutorials/show-function-s.png "SHOW FUNCTION[S]")
Examples:
...SQL> SHOW FUNCTIONS ;
ABS MAXNUM
LOWER SUBSTRLEN
... ...
SQL> SHOW FUNCTION maxnum ;
Function MAXNUM:
Function library is /usr/firebird/udf/ib_udf.so
Entry point is FN_MAX
Returns BY VALUE DOUBLE PRECISION
Argument 1: DOUBLE PRECISION
Argument 2: DOUBLE PRECISION
SHOW GENERATOR[S] displays information about generators declared in the attached database.
SQL> SHOW { GENERATORS | GENERATOR name };![SHOW GENERATOR[S]](/tutorials/show-generator-s.png "SHOW GENERATOR[S]")
Examples:
...SQL> SHOW GENERATORS ;
Generator GEN_EMPNO, Next value: 1234
Generator GEN_JOBNO, Next value: 56789
Generator GEN_ORDNO, Next value: 98765
... ...
SQL> SHOW GENERATOR gen_ordno ;
Generator GEN_ORDNO, Next value: 98765
SHOW GRANT
displays privileges and role ownership information about a named object in the attached database or displays user membership within roles.
SQL> SHOW GRANT { object | rolename };
Examples:
...SQL> SHOW GRANT JOB ;
GRANT SELECT ON JOB TO ALL
GRANT DELETE, INSERT, SELECT, UPDATE ON JOB TO MANAGER
SQL> SHOW GRANT DO_THIS ;
GRANT DO_THIS TO MAGICIAN
SHOW INDEX (SHOW INDICES) displays information about a named index, about indices for a specified table, or about indices for all tables in the attached database. The command can be abbreviated to SHOW IND.
SQL> SHOW {INDICES | INDEX { index | table }};
Examples:
...SQL> SHOW INDEX ;
RDB$PRIMARY1 UNIQUE INDEX ON COUNTRY(COUNTRY)
CUSTNAMEX INDEX ON CUSTOMER(CUSTOMER)
CUSTREGION INDEX ON CUSTOMER(COUNTRY, CITY)
RDB$FOREIGN23 INDEX ON CUSTOMER(COUNTRY)
...
SQL> SHOW IND COUNTRY ;
RDB$PRIMARY20 UNIQUE INDEX ON CUSTOMER(CUSTNO)
CUSTNAMEX INDEX ON CUSTOMER(CUSTOMER)
SHOW PROCEDURE[S]
lists all procedures in the attached database, with their dependencies, or displays the text of the named procedure with the declarations and types (input/output) of any arguments. The command can be abbreviated to SHOW PROC.
SQL> SHOW {PROCEDURES | PROCEDURE name } ;![SHOW PROCEDURE[S]](/tutorials/show-procedure-s.png "SHOW PROCEDURE[S]")
Examples:
SQL> SHOW PROCEDURES ;Procedure Name Dependency Type
================ ======================== =======
ADD_EMP_PROJ EMPLOYEE_PROJECT Table
UNKNOWN_EMP_ID Exception
DELETE_EMPLOYEE DEPARTMENT Table
EMPLOYEE Table
EMPLOYEE_PROJECT Table
...
SQL> SHOW PROC ADD_EMP_PROJ ;
Procedure text:
===============================================================
BEGIN
BEGIN
INSERT INTO EMPLOYEE_PROJECT (
EMP_NO, PROJ_ID)
VALUES (
:emp_no, :proj_id) ;
WHEN SQLCODE -530 DO
EXCEPTION UNKNOWN_EMP_ID;
END
RETURN ;
END
===============================================================
Parameters:
EMP_NO INPUT SMALLINT
PROJ_ID INPUT CHAR(5)
SHOW ROLE[S]
displays the names of SQL roles for the attached database.
SQL> SHOW ROLES ;SHOW ROLES takes no arguments.
Examples:
...SQL> SHOW ROLES ;
MAGICIAN MANAGER
PARIAH SLEEPER
...
To show user membership within roles, use SHOW GRANT rolename.
SHOW SQL DIALECT displays the SQL dialects of the client and of the attached database, if there is one.
SQL> SHOW SQL DIALECT;Example:
...SQL> SHOW SQL DIALECT;
Client SQL dialect is set: 3 and database SQL dialect is: 3
SHOW SYSTEM displays the names of system tables and system views for the attached database. It can be abbreviated to SHOW SYS.
SQL> SHOW SYS [ TABLES ] ;The command takes no arguments. TABLES is an optional keyword that does not affect the behavior of the command.
Examples:
...SQL> SHOW SYS ;
RDB$CHARACTER_SETS RDB$CHECK_CONSTRAINTS
RDB$COLLATIONS RDB$DATABASE
...
SHOW TABLE[S]
lists all tables or views, or displays information about the named table or view.
SQL> SHOW { TABLES | TABLE name };![SHOW TABLE[S]](/tutorials/show-table-s.png "SHOW TABLE[S]")
Examples:
...SQL> SHOW TABLES ;
COUNTRY CUSTOMER
DEPARTMENT EMPLOYEE
EMPLOYEE_PROJECT JOB
...
SQL> SHOW TABLE COUNTRY ;
COUNTRY COUNTRYNAME VARCHAR(15) NOT NULL
CURRENCY VARCHAR(10) NOT NULL
PRIMARY KEY (COUNTRY)
See also SHOW VIEWS (later in the chapter).
SHOW TRIGGER[S] displays all triggers defined in the database, along with the table they depend on or, for the named trigger, displays its sequence, type, activity status (active/inactive), and PSQL definition. It can be abbreviated to SHOW TRIG.
SQL> SHOW {TRIGGERS | TRIGGER name } ;![SHOW TRIGGER[S]](/tutorials/show-trigger-s.png "SHOW TRIGGER[S]")
Examples:
SQL> SHOW TRIGGERS ;Table name Trigger name
================ ========================
EMPLOYEE SET_EMP_NO
EMPLOYEE SAVE_SALARY_CHANGE
CUSTOMER SET_CUST_NO
SALES POST_NEW_ORDER
SQL> SHOW TRIG SET_CUST_NO ;
Trigger:
SET_CUST_NO, Sequence: 0, Type: BEFORE INSERT, Active
AS
BEGIN
new.custno = gen_id(cust_no_gen, 1);
END
SHOW VERSION displays information about the software versions of isql and the Firebird server program, and the on-disk structure of the attached database. It can be abbreviated to SHOW VER.
SQL> SHOW VERSION ;The command takes no arguments. Example, from a server named “dev” running Firebird 1.5 on Windows 2000:
...SQL> SHOW VER ;
ISQL Version: WI-V1.5.0.4306 Firebird 1.5
Firebird/x86/Windows NT (access method),
version "WI-V1.5.0.4306 Firebird 1.5"
Firebird/x86/Windows NT (remote server),
version "WI-V1.5.0.4306 Firebird 1.5/tcp (dev)/P10"
Firebird/x86/Windows NT (remote interface),
version "WI-V1.5.0.4306 Firebird 1.5/tcp (dev)/P10"
on disk structure version 10.1
SHOW VIEW[S] lists all views, or displays information about the named view. (See also SHOW TABLES.)
SQL> SHOW { VIEWS | VIEW name } ;![SHOW VIEW[S]](/tutorials/show-view-s.png "SHOW VIEW[S]")
Example:
SQL> SHOW VIEWS ;PHONE_LIST CUSTOMER
...
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
