Here are some other userful views that we use to get internal data (mostly we build edit forms from these information) :
list all domains with type & length CREATE OR ALTER VIEW OBJ_DOMAINS( DOMAIN_NAME, FIELD_LENGTH, FIELD_CHARLENGTH, FIELD_TYPE) AS SELECT f.rdb$field_name, f.rdb$field_length, f.rdb$field_length, ft.rdb$type_name FROM rdb$fields f, rdb$types ft WHERE f.rdb$field_type = ft.rdb$type AND ft.rdb$field_name = 'RDB$FIELD_TYPE' AND f.rdb$system_flag = 0 AND LEFT(f.rdb$field_name, 4) <> 'RDB$' ORDER BY 1 ; list all procesures' input and output paramteres, and their type/length/etc. (also works with UTF8 or other non-fixed size character sets!) CREATE OR ALTER VIEW OBJ_PROCPARAMS( PROC_TYPE, PROC_NAME, IN_OUT, PARAM_POS, PARAM_NAME, PARAM_TYPE, PARAM_CHARLENGTH, PARAM_SIZE, "DOMAIN") AS SELECT CAST('PROCEDURE' AS VARCHAR(10)), CAST(p.rdb$procedure_name AS VARCHAR(50)), CAST( CASE WHEN p.rdb$parameter_type = 0 THEN 'IN' WHEN p.rdb$parameter_type = 1 THEN 'OUT' ELSE NULL END AS VARCHAR(3)), CAST(p.rdb$parameter_number + 1 AS SMALLINT), CAST(p.rdb$parameter_name AS VARCHAR(50)), CAST(ft.rdb$type_name AS VARCHAR(10)), CAST( CASE WHEN ft.rdb$type_name IN ('BLOB', 'TEXT') THEN NULL WHEN f.rdb$character_length IS NOT NULL THEN f.rdb$character_length WHEN ft.rdb$type_name NOT IN ('BLOB', 'TEXT', 'VARYING') THEN TRUNC(LOG10(POWER(256, f.rdb$field_length)), 0) + 1 ELSE NULL END AS SMALLINT), CAST(IIF(ft.rdb$type_name IN ('BLOB', 'TEXT'), NULL, f.rdb$field_length) AS SMALLINT), CAST(IIF(f.rdb$field_name NOT LIKE 'RDB$%', f.rdb$field_name, NULL) AS VARCHAR(50)) FROM rdb$procedure_parameters p JOIN rdb$fields f ON (f.rdb$field_name = p.rdb$field_source) JOIN rdb$types ft ON (ft.rdb$type = f.rdb$field_type AND ft.rdb$field_name = 'RDB$FIELD_TYPE') UNION SELECT CAST('VIEW' AS VARCHAR(10)), CAST(r.rdb$relation_name AS VARCHAR(50)), CAST('OUT' AS VARCHAR(3)), CAST(v.rdb$field_position + 1 AS SMALLINT), CAST(v.rdb$field_name AS VARCHAR(50)), CAST(ft.rdb$type_name AS VARCHAR(10)), CAST( CASE WHEN ft.rdb$type_name IN ('BLOB', 'TEXT') THEN NULL WHEN f.rdb$character_length IS NOT NULL THEN f.rdb$character_length WHEN ft.rdb$type_name NOT IN ('BLOB', 'TEXT', 'VARYING') THEN TRUNC(LOG10(POWER(256, f.rdb$field_length)), 0) + 1 ELSE NULL -- ezt azert tettem NULL-ra hogy adjon hibat a PHP-ban ha meg valamilyen esetet nem kezeltem le END AS SMALLINT), CAST(IIF(ft.rdb$type_name IN ('BLOB', 'TEXT'), NULL, f.rdb$field_length) AS SMALLINT), CAST(IIF(f.rdb$field_name NOT LIKE 'RDB$%', f.rdb$field_name, NULL) AS VARCHAR(50)) FROM rdb$relations r JOIN rdb$relation_fields v ON (v.rdb$relation_name = r.rdb$relation_name) JOIN rdb$fields f ON (f.rdb$field_name = v.rdb$field_source) JOIN rdb$types ft ON (ft.rdb$type = f.rdb$field_type AND ft.rdb$field_name = 'RDB$FIELD_TYPE') WHERE r.rdb$view_blr IS NOT NULL AND COALESCE(r.rdb$system_flag, 0) = 0 ORDER BY 1, 2, 3, 4 ; lists all procedures and views CREATE OR ALTER VIEW OBJ_PROCVIEWS( OBJECT_NAME, OBJECT_TYPE, SUBTYPE) AS SELECT CAST(rdb$procedure_name AS VARCHAR(10)), CAST('PROCEDURE' AS VARCHAR(10)), CAST( CASE WHEN rdb$procedure_type = 1 THEN 'SELECT' WHEN rdb$procedure_type = 2 THEN 'EXECUTE' ELSE '' END AS VARCHAR(10)) FROM rdb$procedures UNION SELECT CAST(rdb$relation_name AS VARCHAR(10)), CAST('VIEW' AS VARCHAR(10)), CAST('SELECT' AS VARCHAR(10)) FROM rdb$relations WHERE rdb$view_blr IS NOT NULL AND (rdb$system_flag IS NULL OR rdb$system_flag = 0) ORDER BY 1 ; lists all relations between tables (this can be very useful when deleting a record that is locked by a child table...) CREATE OR ALTER VIEW SYS$OBJ_RELATIONS( FROM_TABLE, FROM_FIELD, TO_TABLE, TO_FIELD) AS SELECT from_table.rdb$relation_name AS from_table, from_field.rdb$field_name AS from_field, to_table.rdb$relation_name to_table, to_field.rdb$field_name AS to_field FROM rdb$indices from_table INNER JOIN rdb$index_segments from_field ON from_field.rdb$index_name = from_table.rdb$index_name INNER JOIN rdb$indices to_table ON to_table.rdb$index_name = from_table.rdb$foreign_key INNER JOIN rdb$index_segments to_field ON to_table.rdb$index_name = to_field.rdb$index_name WHERE from_table.rdb$foreign_key IS NOT NULL ; these are what we use most often....i hope i made someone's life easier a bit :) Cheers, Szilvi ________________________________ From: W O <sistemas2000profesio...@gmail.com> To: firebird-support@yahoogroups.com Sent: Thursday, February 14, 2013 10:30 PM Subject: Re: [firebird-support] Retrieve fieldnames AND types Martijn, can you send that presentation to me, too? I'm very interested. Greetings. Walter. On Thu, Feb 14, 2013 at 8:19 AM, Martijn Tonies <m.ton...@upscene.com>wrote: > ** > > > Hello Michael, > > I'll send you one of my old presentations about the system tables. > > With regards, > > Martijn Tonies > Upscene Productions > http://www.upscene.com > > Download Database Workbench for Oracle, MS SQL Server, Sybase SQL > Anywhere, MySQL, InterBase, NexusDB and Firebird! > > > > I have found the way to retrieve fieldnames to a table via SQL. > > Like this: > > > > select > > RDB$FIELD_NAME > > from > > RDB$RELATION_FIELDS > > Where > > RDB$Relation_Name='MYTABLE' > > > > > > But I could use the SQL to ALSO retrieve the field type, but I am not > sure > > where to find this information > > > > COuld someone provide me with proper SQL? > > > > Regards > > Mcahel > > > > > > > > ------------------------------------ > > > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > > > Visit http://www.firebirdsql.org and click the Resources item > > on the main (top) menu. Try Knowledgebase and FAQ links ! > > > > Also search the knowledgebases at http://www.ibphoenix.com > > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Yahoo! Groups Links > > > > > > > > > [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links [Non-text portions of this message have been removed]