Lorenzo Alberton
« 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.
Related articles
- Firebird 2 cheat sheet
- Extracting META information from Oracle (INFORMATION_SCHEMA)
- Extracting META information from PostgreSQL (INFORMATION_SCHEMA)
- Extracting META information from SQL Server (INFORMATION_SCHEMA)
- Updated articles to extract META informations from databases
Latest articles
- On batching vs. latency, and jobqueue models
- Updated Kafka PHP client library
- Musings on some technical papers I read this weekend: Google Dremel, NoSQL comparison, Gossip Protocols
- Historical Twitter access - A journey into optimising Hadoop jobs
- Kafka proposed as Apache incubator project
- NoSQL Databases: What, When and Why (PHPUK2011)
- PHPNW10 slides and new job!
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 FrameworkFollow @lorenzoalberton
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:
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
http://www.dbsoftlab.com/visual-importer/972-duplicated-field-names.htmlThis one does not.
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"):
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:
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:
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:
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:
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
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.