Excellent Nagy, thank you very much.

Greetings.

Walter.





On Thu, Feb 14, 2013 at 4:42 PM, Nagy Szilveszter <
nagy_szilveszter@...> 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 sistemas2000profesional@...>
> 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.tonies@...
> >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]