| Subject | Re: [firebird-support] Retrieve fieldnames AND types |
|---|---|
| Author | Nagy Szilveszter |
| Post date | 2013-02-14T20:42:34Z |
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.
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]