http://www.alberton.info/firebird_sql_meta_info.html
Interbase/Firebird INFORMATION_SCHEMA
The SQL 2003 Standard introduced a new schema called INFORMATION_SCHEMA. PostgreSQL, SQL Server and now MySQL have it. ORACLE, DB2, Sybase, Ingres, Informix and other DBMS have something similar, usually called System Tables. The INFORMATION_SCHEMA is meant to be a set of views you can query using regular SELECT statements, for instance if you need to know something about the defined triggers, or the structure of a table to which you have access. Firebird doesn't have it, but you can retrieve pretty much everything you need from the system tables. These are metadata tables, their names start with "RDB$". Let's see how we can retrieve some useful informations from them.
Test data
We need a few sample tables, indices and views to test the following queries, so let's create them. We also create a sample TRIGGER to emulate the autoincrement feature of mysql, and a simple stored procedure.
-- sample data to test Firebird system tables
-- TABLE TEST
CREATE TABLE TEST (
TEST_NAME CHAR(30) CHARACTER SET NONE NOT NULL COLLATE NONE,
TEST_ID INTEGER DEFAULT '0' NOT NULL,
TEST_DATE TIMESTAMP NOT NULL
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);
-- TABLE TEST2 with some CONSTRAINTs and an INDEX
CREATE TABLE TEST2 (
ID INTEGER NOT NULL,
FIELD1 INTEGER,
FIELD2 CHAR(15) CHARACTER SET NONE COLLATE NONE,
FIELD3 VARCHAR(50) CHARACTER SET NONE COLLATE NONE,
FIELD4 INTEGER,
FIELD5 INTEGER,
ID2 INTEGER NOT NULL
);
ALTER TABLE TEST2 ADD CONSTRAINT PRIMARY KEY (ID2);
CREATE UNIQUE INDEX TEST2_FIELD1ID_IDX ON TEST2(ID, FIELD1);
CREATE UNIQUE INDEX TEST2_FIELD4_IDX ON TEST2(FIELD4);
CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);
-- TABLE NUMBERS
CREATE TABLE NUMBERS (
NUMBER INTEGER DEFAULT '0' NOT NULL,
EN CHAR(100) CHARACTER SET ISO8859_1 NOT NULL COLLATE ISO8859_1,
FR CHAR(100) CHARACTER SET ISO8859_1 NOT NULL COLLATE ISO8859_1
);
-- TABLE NEWTABLE
CREATE TABLE NEWTABLE (
ID INT DEFAULT 0 NOT NULL,
SOMENAME VARCHAR (12),
SOMEDESCRIPTION VARCHAR (12)
);
ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);
CREATE GENERATOR NEWTABLE_SEQ;
-- VIEW on TEST
CREATE VIEW "testview"(
TEST_NAME,
TEST_ID,
TEST_DATE
) AS
SELECT *
FROM TEST
WHERE TEST_NAME LIKE 't%';
-- VIEW on NUMBERS
CREATE VIEW "numbersview"(
NUMBER,
TRANS_EN,
TRANS_FR
) AS
SELECT *
FROM NUMBERS
WHERE NUMBER > 100;
-- TRIGGER on NEWTABLE (emulate autoincrement)
SET TERM ^ ;
CREATE TRIGGER AUTOINCREMENTPK FOR NEWTABLE
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.ID IS NULL OR NEW.ID = 0) THEN
NEW.ID = GEN_ID(NEWTABLE_SEQ, 1);
END^
SET TERM ; ^
-- SAMPLE STORED PROCEDURE
SET TERM ^ ;
CREATE PROCEDURE getEnglishNumber(N INTEGER)
RETURNS (
english_number CHAR(100)
)
AS
BEGIN
FOR
SELECT EN
FROM NUMBERS
WHERE NUMBER = :N
INTO :english_number
DO
BEGIN
SUSPEND;
END
END ^
SET TERM ; ^
List TABLEs
Here's the query that will return the names of the tables defined in the current database:
SELECT DISTINCT RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0;
-- or
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0;
NB: the above queries will list both the user-defined tables AND views. To exclude the VIEWs from the resultset, you can write one of these queries:
SELECT DISTINCT RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0
AND RDB$VIEW_CONTEXT IS NULL;
-- or
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG=0
AND RDB$VIEW_BLR IS NULL;
List VIEWs
Here's the query that will return the names of the VIEWs defined in the current database:
SELECT DISTINCT RDB$VIEW_NAME
FROM RDB$VIEW_RELATIONS;
-- show only the VIEWs referencing a given table
SELECT DISTINCT RDB$VIEW_NAME
FROM RDB$VIEW_RELATIONS
WHERE RDB$RELATION_NAME='TEST';
List users
SELECT DISTINCT RDB$USER
FROM RDB$USER_PRIVILEGES;
List INDICES
Here's the query that will return the names of the INDICES defined in the TEST2 table.
NB: the CONSTRAINTs are not listed
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$RELATION_NAME='TEST2'
AND RDB$UNIQUE_FLAG IS NULL
AND RDB$FOREIGN_KEY IS NULL;
List CONSTRAINTs
Here's the query that will return the names of the CONSTRAINTs defined in the TEST2 table:
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$RELATION_NAME='TEST2'
AND (
RDB$UNIQUE_FLAG IS NOT NULL
OR RDB$FOREIGN_KEY IS NOT NULL
);
List table fields
Here's the query that will return the names of the fields of the TEST2 table:
SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='TEST2';
If you want some more info about the field definitions, you can retrieve a larger subset of the fields available in the RDB$RELATIONS_FIELDS table:
SELECT RDB$FIELD_NAME AS field_name,
RDB$FIELD_POSITION AS field_position,
RDB$DESCRIPTION AS field_description,
RDB$DEFAULT_VALUE AS field_default_value,
RDB$NULL_FLAG AS field_not_null_constraint
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='TEST2';
List GENERATORs (sequences)
A GENERATOR is a sequential number that can be automatically inserted in a column with the GEN_ID() function. A GENERATOR is often used to ensure a unique value in a PRIMARY KEY that must uniquely identify the associated row.
SELECT RDB$GENERATOR_NAME
FROM RDB$GENERATORS
WHERE RDB$SYSTEM_FLAG IS NULL;
List TRIGGERs
SELECT * FROM RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL;
-- list only the triggers for a given table
SELECT * FROM RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL
AND RDB$RELATION_NAME='NEWTABLE';
List FUNCTIONs (UDF)
SELECT * FROM RDB$FUNCTIONS
WHERE RDB$SYSTEM_FLAG IS NULL;
List Stored Procedures
SELECT * FROM RDB$PROCEDURES;
What else?
System tables are a powerful tool in the hands of the Interbase/Firebird db admins. The queries listed in this page are just the top of the iceberg, you can retrieve a lot more from these tables. If you'd like to see some other examples, or have some comments and/or suggestions, just drop me a mail (you can find my address in the footer of this page) and I'll add them to this list.
HTH. |
