Lorenzo Alberton

London, UK   ·   Contact me   ·  

« Articles

Extracting META information from Interbase/Firebird SQL (INFORMATION_SCHEMA)

Abstract: Extracting META informations from Interbase/Firebird System Tables (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"(
  NUM,
  EN,
  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;

Detailed INDEX info

If you want to know which table columns are referenced by an index, try with this query:

SELECT RDB$INDEX_SEGMENTS.RDB$FIELD_NAME AS field_name,
          RDB$INDICES.RDB$DESCRIPTION AS description,
          (RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION + 1) AS field_position
     FROM RDB$INDEX_SEGMENTS
LEFT JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME
    WHERE UPPER(RDB$INDICES.RDB$RELATION_NAME)='TEST2'         -- table name
      AND UPPER(RDB$INDICES.RDB$INDEX_NAME)='TEST2_FIELD5_IDX' -- index name
      AND RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE IS NULL
 ORDER BY RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION

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' -- table name
   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';  -- table name

Detailed table field info

If you want a really detailed description of the field, you have to join the RDB$RELATIONS_FIELDS table with RDB$FIELDS:

SELECT r.RDB$FIELD_NAME AS field_name,
        r.RDB$DESCRIPTION AS field_description,
        r.RDB$DEFAULT_VALUE AS field_default_value,
        r.RDB$NULL_FLAG AS field_not_null_constraint,
        f.RDB$FIELD_LENGTH AS field_length,
        f.RDB$FIELD_PRECISION AS field_precision,
        f.RDB$FIELD_SCALE AS field_scale,
        CASE f.RDB$FIELD_TYPE
          WHEN 261 THEN 'BLOB'
          WHEN 14 THEN 'CHAR'
          WHEN 40 THEN 'CSTRING'
          WHEN 11 THEN 'D_FLOAT'
          WHEN 27 THEN 'DOUBLE'
          WHEN 10 THEN 'FLOAT'
          WHEN 16 THEN 'INT64'
          WHEN 8 THEN 'INTEGER'
          WHEN 9 THEN 'QUAD'
          WHEN 7 THEN 'SMALLINT'
          WHEN 12 THEN 'DATE'
          WHEN 13 THEN 'TIME'
          WHEN 35 THEN 'TIMESTAMP'
          WHEN 37 THEN 'VARCHAR'
          ELSE 'UNKNOWN'
        END AS field_type,
        f.RDB$FIELD_SUB_TYPE AS field_subtype,
        coll.RDB$COLLATION_NAME AS field_collation,
        cset.RDB$CHARACTER_SET_NAME AS field_charset
   FROM RDB$RELATION_FIELDS r
   LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
   LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
   LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
  WHERE r.RDB$RELATION_NAME='TEST2'  -- table name
ORDER BY r.RDB$FIELD_POSITION;

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=0;

List TRIGGERs

SELECT * FROM RDB$TRIGGERS
 WHERE RDB$SYSTEM_FLAG=0;

-- list only the triggers for a given table

SELECT * FROM RDB$TRIGGERS
 WHERE RDB$SYSTEM_FLAG = 0
   AND RDB$RELATION_NAME='NEWTABLE';  -- table name

List FUNCTIONs (UDF)

SELECT * FROM RDB$FUNCTIONS
 WHERE RDB$SYSTEM_FLAG=0;

List Stored Procedures

SELECT * FROM RDB$PROCEDURES;

List FOREIGN KEY constraints

As a bonus, I'm going to show how to get more information for the FOREIGN KEY constraints on a table, i.e. one query that returns the FK names, the names of the table and field they're tied to, the names of the table and field they reference and the action on update/delete. These are the two test tables and the FK constraint:

CREATE TABLE "a" (
  "id" INTEGER NOT NULL,
  "name" VARCHAR(20) DEFAULT '' NOT NULL
);
ALTER TABLE "a" ADD PRIMARY KEY ("id");

CREATE TABLE "b" (
  "id" INTEGER NOT NULL,
  "a_id" INTEGER DEFAULT 0 NOT NULL
);
ALTER TABLE "b" ADD PRIMARY KEY ("id");
ALTER TABLE "b" ADD CONSTRAINT "FK_b" FOREIGN KEY ("a_id")
REFERENCES "a"("id") ON DELETE CASCADE ON UPDATE CASCADE;

And this is the query that will return the above mentioned values (replace the table name in the last line to get the FK definitions relative to another table):

SELECT DISTINCT
          rc.RDB$CONSTRAINT_NAME AS "constraint_name",
          rc.RDB$RELATION_NAME AS "on table",
          d1.RDB$FIELD_NAME AS "on field",
          d2.RDB$DEPENDED_ON_NAME AS "references table",
          d2.RDB$FIELD_NAME AS "references field",
          refc.RDB$UPDATE_RULE AS "on update",
          refc.RDB$DELETE_RULE AS "on delete"
     FROM RDB$RELATION_CONSTRAINTS AS rc
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME
LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
    WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME
      AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME
      AND rc.RDB$RELATION_NAME = 'b'  -- table name

Detailed CONSTRAINT info

If you want to retrieve detailed info from any constraint (fields, type, rules, referenced table and fields for FOREIGN KEYs, etc.) given its name and table, here's the query to do so:

SELECT rc.RDB$CONSTRAINT_NAME,
          s.RDB$FIELD_NAME AS field_name,
          rc.RDB$CONSTRAINT_TYPE AS constraint_type,
          i.RDB$DESCRIPTION AS description,
          rc.RDB$DEFERRABLE AS is_deferrable,
          rc.RDB$INITIALLY_DEFERRED AS is_deferred,
          refc.RDB$UPDATE_RULE AS on_update,
          refc.RDB$DELETE_RULE AS on_delete,
          refc.RDB$MATCH_OPTION AS match_type,
          i2.RDB$RELATION_NAME AS references_table,
          s2.RDB$FIELD_NAME AS references_field,
          (s.RDB$FIELD_POSITION + 1) AS field_position
     FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
    WHERE i.RDB$RELATION_NAME='b'       -- table name
      AND rc.RDB$CONSTRAINT_NAME='FK_b' -- constraint name
      AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL
 ORDER BY s.RDB$FIELD_POSITION

Detailed TRIGGER info

SELECT RDB$TRIGGER_NAME AS trigger_name,
       RDB$RELATION_NAME AS table_name,
       RDB$TRIGGER_SOURCE AS trigger_body,
       CASE RDB$TRIGGER_TYPE
        WHEN 1 THEN 'BEFORE'
        WHEN 2 THEN 'AFTER'
        WHEN 3 THEN 'BEFORE'
        WHEN 4 THEN 'AFTER'
        WHEN 5 THEN 'BEFORE'
        WHEN 6 THEN 'AFTER'
       END AS trigger_type,
       CASE RDB$TRIGGER_TYPE
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE'
        WHEN 4 THEN 'UPDATE'
        WHEN 5 THEN 'DELETE'
        WHEN 6 THEN 'DELETE'
       END AS trigger_event,
       CASE RDB$TRIGGER_INACTIVE
        WHEN 1 THEN 0 ELSE 1
       END AS trigger_enabled,
       RDB$DESCRIPTION AS trigger_comment
  FROM RDB$TRIGGERS
 WHERE UPPER(RDB$TRIGGER_NAME)='AUTOINCREMENTPK'

Detailed VIEW info

If, given a VIEW name, you want to retrieve the field aliases and the field names in the original table, you can run this query:

SELECT d.RDB$DEPENDENT_NAME AS view_name, 
         r.RDB$FIELD_NAME AS field_name,
         d.RDB$DEPENDED_ON_NAME AS depended_on_table,
         d.RDB$FIELD_NAME AS depended_on_field
    FROM RDB$DEPENDENCIES d 
    LEFT JOIN RDB$RELATION_FIELDS r ON d.RDB$DEPENDENT_NAME = r.RDB$RELATION_NAME 
         AND d.RDB$FIELD_NAME = r.RDB$BASE_FIELD
   WHERE UPPER(d.RDB$DEPENDENT_NAME)='NUMBERSVIEW' 
     AND r.RDB$SYSTEM_FLAG = 0
     AND d.RDB$DEPENDENT_TYPE = 1 --VIEW
ORDER BY r.RDB$FIELD_POSITION

If you want to get the VIEW definition, though, the above query is not enough. It will only show the matches between aliases and real fields. Use the following query to get the VIEW body:

SELECT RDB$VIEW_SOURCE
  FROM RDB$RELATIONS
 WHERE RDB$VIEW_SOURCE IS NOT NULL
   AND UPPER(RDB$RELATION_NAME) = 'NUMBERSVIEW';

One last tip

Since Firebird 2.1, you can query the database for some system information, like the engine version, the network protocol, etc. Have a look at the doc/sql.extensions/README.context_variables2.txt file for a listing of all the system variables that are available. Here's an example of what you can do:

SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION') AS engine_version,
       RDB$GET_CONTEXT('SYSTEM', 'NETWORK_PROTOCOL') AS protocol,
       RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS') AS address
  FROM RDB$DATABASE;

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.



33 responses to "Extracting META information from Interbase/Firebird SQL (INFORMATION_SCHEMA)"

Jaen, 06 August 2009 13:04

Great article, thanks.

Sofi, 27 August 2009 13:40

This is really great article! I was wondering what system tables are affected when data is inserted, updated, deleted... to ordinary tables. If there are any... I\'m interested in adding a trigger to such table to add some global functionality if it is possible.
Thanks!

Albert, 27 August 2009 22:04

Nice article, but when I executed the query to extract Foreign Keys information on Firebird 2.1.1.17910, I didn't get any rows, though I have foreign keys on my tables. After playing a while with metadata tables, I've discover right query:

 select 
	 rc.rdb$constraint_name AS fk_name
	, rcc.rdb$relation_name AS child_table
	, isc.rdb$field_name AS child_column
	, rcp.rdb$relation_name AS parent_table
	, isp.rdb$field_name AS parent_column
	, rc.rdb$update_rule AS update_rule
	, rc.rdb$delete_rule AS delete_rule
from rdb$ref_constraints AS rc
	inner join rdb$relation_constraints AS rcc
		on rc.rdb$constraint_name = rcc.rdb$constraint_name
	inner join rdb$index_segments AS isc
		on rcc.rdb$index_name = isc.rdb$index_name
	inner join rdb$relation_constraints AS rcp
		on rc.rdb$const_name_uq  = rcp.rdb$constraint_name
	inner join rdb$index_segments AS isp
		on rcp.rdb$index_name = isp.rdb$index_name

Ady, 07 September 2009 11:05

Hi,
Nice article and great work. I tried obtaining the dependencies of a table, particularly the stored procedures that depend on a table. What's strange is that the RDB$DEPENDENCIES table returns a few rows indicating certain procedures depend on the table but the RDB$PROCEDURES indicates that there are no stored procedures in the database. Is the RDB$DEPENDENCIES content outdated? Do I need to update/sync it before querying it?

Lorenzo Alberton, 08 September 2009 00:10

@Ady, I don't think the content of the system tables ever gets outdated. What's the value of the RDB$DEPENDENCIES.RDB$DEPENDENT_TYPE field for your stored procedure? If it's 5, there should be an entry in the RDB$PROCEDURES table as well.

Hans, 03 October 2009 18:53

When listing triggers the correct WHERE statement should be
WHERE RDB$SYSTEM_FLAG = 0
not IS NULL

Lorenzo Alberton, 03 October 2009 19:17

@Hans, that depends on what Firebird version you are using. The safest thing to do is to check for both: WHERE (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)

David Ballantyne, 22 October 2009 16:09

Hi Lorenzo;
Im a dbms software tools developer, currently working on ISO Information Schema objects for DBMS\'s that do not have an ISO standard implementation (e.g. firebird, oracle etc).
Just as a point of clarrification, ISO Information Schema views were introduced in the \'92 SQL Standard, then updated in both the \'99 and \'2003 Standards. As far as I am aware, there are currently no DMBS implementations based on the \'99 or \'2003 extensions.
I am also hoping that once complete, the Firebird foundation, may be interested in implementing the ISO Information Schema views, which I will be more than happy to dontate once complete and tested.

Moez Slimi, 28 March 2010 19:42

Great article !! Firebird is the best choice for database centric development. Keep it coming :)

evilripper, 14 June 2010 09:34

ottimo articolo! grazie, mi hai fatto risparmiare parecchio tempo!

Ivo, 21 July 2010 11:29

This article was of great help. Thank you!

Mikhail, 03 August 2010 07:47

Thanks a lot! Found here what I was looking for!

Mike, 11 September 2010 20:10

The SQL to extract fields information returns duplicates
This one does not.

   SELECT r.RDB$FIELD_NAME AS field_name,
          r.RDB$DESCRIPTION AS field_description,
          r.RDB$DEFAULT_VALUE AS field_default_value,
          r.RDB$NULL_FLAG AS field_not_null_constraint,
          f.RDB$FIELD_LENGTH AS field_length,
          f.RDB$FIELD_PRECISION AS field_precision,
          f.RDB$FIELD_SCALE AS field_scale,
          CASE f.RDB$FIELD_TYPE
              WHEN 261 THEN 'BLOB'
              WHEN 14 THEN 'CHAR'
              WHEN 40 THEN 'CSTRING'
              WHEN 11 THEN 'D_FLOAT'
              WHEN 27 THEN 'DOUBLE'
              WHEN 10 THEN 'FLOAT'
              WHEN 16 THEN 'INT64'
              WHEN 8 THEN 'INTEGER'
              WHEN 9 THEN 'QUAD'
              WHEN 7 THEN 'SMALLINT'
              WHEN 12 THEN 'DATE'
              WHEN 13 THEN 'TIME'
              WHEN 35 THEN 'TIMESTAMP'
              WHEN 37 THEN 'VARCHAR'
              ELSE 'UNKNOWN'
          END AS field_type,
          f.RDB$FIELD_SUB_TYPE AS field_subtype
     FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
    WHERE r.RDB$RELATION_NAME='DEPARTMENT' -- table name
 ORDER BY r.RDB$FIELD_POSITION;
http://www.dbsoftlab.com/visual-importer/972-duplicated-field-names.html

MoerBoer, 28 September 2010 13:47

Hi Lorenzo;
Thanks for these, they really help. I'm struggling a bit with the Detailed CONSTRAINT info. When I do the statement, I get duplicate / triplicates etc. on some tables. Any ideas?

bateramos, 01 March 2011 11:54

this article rocks!!!!

Gorka, 17 March 2011 13:56

Hi, I think that there's a typo in "List FOREIGN KEY constraints", in the select (line 9) the AS has to be removed instead of FROM RDB$RELATION_CONSTRAINTS AS rc type this FROM RDB$RELATION_CONSTRAINTS rc

Gorka, 17 March 2011 18:43

Hi again, I'm trying to get something, apparently easy, like this MySQL statement show columns from "table". What I need is to know which fields of the table are primary keys. I've trying to get it but I cannot join the fields with their "primary key" constraints. Could you point me the way for doing it?
Thank very much in advance,
Gorka

Lorenzo Alberton, 19 March 2011 11:28

@Gorka: Regarding your first comment, the "AS" keyword is correct, albeit optional. I tend to use it to make it clear it's an alias.
I believe this is the query you're looking for (similar to MySQL's "SHOT COLUMNS FROM tbl"):

   SELECT r.RDB$FIELD_NAME AS "Field",
          CASE f.RDB$FIELD_TYPE
            WHEN 261 THEN 'BLOB'
            WHEN 14 THEN 'CHAR'
            WHEN 40 THEN 'CSTRING'
            WHEN 11 THEN 'D_FLOAT'
            WHEN 27 THEN 'DOUBLE'
            WHEN 10 THEN 'FLOAT'
            WHEN 16 THEN 'INT64'
            WHEN 8 THEN 'INTEGER'
            WHEN 9 THEN 'QUAD'
            WHEN 7 THEN 'SMALLINT'
            WHEN 12 THEN 'DATE'
            WHEN 13 THEN 'TIME'
            WHEN 35 THEN 'TIMESTAMP'
            WHEN 37 THEN 'VARCHAR'
            ELSE 'UNKNOWN'
          END AS "Type",
          f.RDB$FIELD_LENGTH AS "Length",
          f.RDB$FIELD_PRECISION AS "Precision",
          f.RDB$FIELD_SCALE AS "Scale",
          MIN(rc.RDB$CONSTRAINT_TYPE) AS "Constraint",
          MIN(i.RDB$INDEX_NAME) AS "Idx",
          CASE WHEN r.RDB$NULL_FLAG = 1 THEN 'NO' ELSE 'YES' END AS "Null",
          r.RDB$DEFAULT_VALUE AS "Default",
          r.RDB$FIELD_POSITION AS "Pos" 
     FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s ON s.RDB$FIELD_NAME=r.RDB$FIELD_NAME
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME 
      AND i.RDB$RELATION_NAME=r.RDB$RELATION_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
      AND rc.RDB$INDEX_NAME = i.RDB$INDEX_NAME
      AND rc.RDB$RELATION_NAME = i.RDB$RELATION_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
    WHERE r.RDB$RELATION_NAME='TEST2'  -- table name
 GROUP BY "Field",
          "Type", 
          "Length",
          "Precision",
          "Scale",
          "Null",
          "Default",
          "Pos"
 ORDER BY "Pos";

The "Idx" field is useful to match fields in compound keys.
HTH

Giji, 25 April 2011 07:58

I need to sync SQL server database with the Interbase DB. Interbase is the live DB and main application works on this. Now customer wants to create an SQL server database and sync it with the live interbase database once everyday. How can I identify the new rows or modified columns in Interbase. The Interbase DB does not have timestamp or any column that contains time. Hence I cound not found any way to identify new (and modified also) records. Any idea?

poerte, 04 May 2011 20:26

thank you sooo much this article really help me

Manoj, 26 July 2011 09:35

HOW TO KNOW THE NUMBER OF COLUMNS IN A TABLE

Lorenzo Alberton, 26 July 2011 10:18

@Manoj:

SELECT COUNT(RDB$FIELD_NAME) AS n_cols 
  FROM RDB$RELATION_FIELDS 
 WHERE RDB$RELATION_NAME='TEST2';

Manoj, 27 July 2011 09:14

HI lORENZO THANKS FOR UR RESPONSE....WHAT ARE THE MAJOR ADVANTAGES IN IB WHEN YOU COMPARED TO OTHER DATABASE .....AND HOW TO COUNT NUMBER OF ROWS IN A TABLE........WAT ARE THE MAIN DIFFERENCE BETWEEN SQL AND IB DATABASE

Matheus, 25 October 2011 14:25

@Albert, Your query is almost what I needed. There were a couple of cases where it broke though: when the name of the index created by the RDBMS did not match the constraint name and similarly when the field names of the relashionship were not equal. This one fixes that:

   SELECT rc.RDB$RELATION_NAME AS "fk_table", 
          flds_fk.rdb$field_name as "fk_field", 
          rc2.rdb$relation_name as "pk_table", 
          flds_pk.rdb$field_name as "pk_field", 
          rc.RDB$CONSTRAINT_NAME AS "constraint_name", 
          rfc.RDB$CONST_NAME_UQ as "to_index", 
          flds_fk.rdb$field_position as "position" 
     FROM RDB$RELATION_CONSTRAINTS AS rc 
LEFT JOIN rdb$ref_constraints AS rfc ON rc.RDB$CONSTRAINT_NAME = rfc.RDB$CONSTRAINT_NAME
LEFT JOIN rdb$index_segments AS flds_fk ON flds_fk.rdb$index_name = rc.rdb$index_name 
LEFT JOIN rdb$relation_constraints AS rc2 ON rc2.rdb$constraint_name = rfc.rdb$const_name_uq
LEFT JOIN rdb$index_segments AS flds_pk ON flds_pk.rdb$index_name = rc2.rdb$index_name
      AND flds_fk.rdb$field_position = flds_pk.rdb$field_position
    WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' 
    --AND rc.RDB$RELATION_NAME = 'tablename' 
    --AND rc.RDB$CONSTRAINT_NAME = 'fk_name' 
 ORDER BY rc.RDB$CONSTRAINT_NAME, flds_fk.rdb$field_position

bzjr, 21 November 2011 08:44

Many Thanks, very useful article.

NewbieSQL, 13 December 2011 15:40

How to get last inserted record(row) in all tabels in database?

Rose, 02 January 2012 07:20

Hi Lorenzo, Is there a way to find out the number of columns retrieved from select query?
Eg: select xx, yy from trade where symbol=\'MSFT\';
Here its obvious that it is 2, I need to pass this num_cols to another function but do not want to hardcode it. Just like count(*) returns the number of rows, is there a way to know the number of columns from select query? Please help?
Thanks

Leandro Martins das Neves, 01 March 2012 18:25

Thanks!Thanks!Thanks!Thanks!Thanks!Thanks! That\'s all I have to say!

Leandro Martins das Neves, 13 March 2012 21:01

Ladies & Gentlemen, The statment below perfectly brings the primary key field names. The statment above proved not to work in all cases in my Firebird installation, especially on those tables with multi-field primary key. This one proved to be effective:

   SELECT s.rdb$field_name 
     FROM rdb$index_segments AS s 
LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) 
    WHERE rc.rdb$relation_name = 'CTRECEB' 
      AND rc.rdb$constraint_type = 'PRIMARY KEY' 
I hope it may be helpful!

Thatchi, 27 March 2012 17:26

Great Article ! :) very useful info. Thanks.
List of Users =============== SELECT DISTINCT RDB$USER FROM RDB$USER_PRIVILEGES;
This does not give all the users it gives only the users who are currently logged in. How to get all the users in the security db ?

keith, 09 April 2012 03:25

This is a great article. My Delphi SQL access routines are improving by leaps and bounds. I have another variation on the columns info lookup that you may be interested in. Here is what I have done:

   SELECT r.RDB$FIELD_NAME AS "Field", 
          f.RDB$FIELD_TYPE AS "DataType", 
          typ.RDB$TYPE_NAME AS "TypeName", 
          CASE WHEN typ.RDB$TYPE_NAME = 'BLOB' THEN f.RDB$FIELD_SUB_TYPE ELSE '' END AS "SubType", 
          CASE WHEN typ.RDB$TYPE_NAME = 'BLOB' THEN sub.RDB$TYPE_NAME ELSE '' END AS "SubTypeName", 
          f.RDB$FIELD_LENGTH AS "Length", 
          f.RDB$FIELD_PRECISION AS "Precision", 
          f.RDB$FIELD_SCALE AS "Scale", 
          MIN(rc.RDB$CONSTRAINT_TYPE) AS "Constraint", 
          MIN(i.RDB$INDEX_NAME) AS "Idx", 
          CASE WHEN r.RDB$NULL_FLAG = 1 THEN 'NO' ELSE 'YES' END AS "Null", 
          r.RDB$DEFAULT_VALUE AS "Default", 
          r.RDB$FIELD_POSITION AS "Pos" 
     FROM RDB$RELATION_FIELDS r 
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME 
LEFT JOIN RDB$INDEX_SEGMENTS s ON s.RDB$FIELD_NAME=r.RDB$FIELD_NAME 
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME 
      AND i.RDB$RELATION_NAME = r.RDB$RELATION_NAME 
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME 
      AND rc.RDB$INDEX_NAME = i.RDB$INDEX_NAME AND rc.RDB$RELATION_NAME = i.RDB$RELATION_NAME 
LEFT JOIN RDB$REF_CONSTRAINTS REFC ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME 
LEFT JOIN RDB$TYPES typ ON typ.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' 
      AND typ.RDB$TYPE = f.RDB$FIELD_TYPE 
LEFT JOIN RDB$TYPES sub ON sub.RDB$FIELD_NAME = 'RDB$FIELD_SUB_TYPE' 
      AND sub.RDB$TYPE = f.RDB$FIELD_SUB_TYPE 
    WHERE r.RDB$RELATION_NAME='PLAYERCONTACT' 
 GROUP BY "Field", 
          "DataType", 
          "TypeName", 
          "SubType", 
          "SubTypeName", 
          "Length", 
          "Precision", 
          "Scale", 
          "Null", 
          "Default", 
          "Pos" 
 ORDER BY "Pos"
You will notice that I am getting the datatype name from the RDB$TYPE table instead of a hardcoded CASE statement. And I retrieve the BLOB subtype and subtype names. (Sorry about the readability. How do you guys get that pretty formatting without HTML?) I hope it may be helpful!

Diego de la Fuente, 10 April 2012 18:43

Script to Check PK for any table

 
   SELECT s.rdb$field_name 
     FROM rdb$index_segments s 
LEFT JOIN rdb$relation_constraints rc ON (rc.rdb$index_name = s.rdb$index_name) 
    WHERE rc.rdb$relation_name = '<Your table name>' 
      AND rc.rdb$constraint_type = 'PRIMARY KEY' 
change "<Your table name>" with a real table name like 'PRODUCTS'

Brian, 16 August 2012 20:43

Just wanted to say thank you. Very useful information and the cheat sheet is also great.


Related articles

Latest articles

Filter articles by topic

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework


Back