Excellent Nagy, thank you very much. Greetings.
Walter. On Thu, Feb 14, 2013 at 4:42 PM, Nagy Szilveszter < [email protected]> wrote: > ** > > > 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 [email protected]> > To: [email protected] > 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 [email protected] > >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] > > > [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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
