The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Describe table structure in firebird?

Hello,

How can you view the structure of a table in firebird using isql? e.g. similar to oracle's describe table;

Thanks.
j2e
Tuesday, January 03, 2006
 
 
Have you tried "show table <tablename>;"?
Bert Neef
Tuesday, January 03, 2006
 
 
While you are at it...in firebird, I can't find the proper syntax for "if tablename exists drop it".

I've googled it to death, no luck.
sharkfish Send private email
Tuesday, January 03, 2006
 
 
"Recreate table ..." is the syntax for "drop if exists"...
Don't know about the describe though...
Fritz Send private email
Tuesday, January 03, 2006
 
 
You can query the system tables:

SELECT

RDB$RELATION_FIELDS.RDB$FIELD_NAME AS COLUMN_NAME,
RDB$TYPES.RDB$TYPE_NAME AS DATA_TYPE

FROM RDB$RELATIONS
INNER JOIN RDB$RELATION_FIELDS ON RDB$RELATIONS.RDB$RELATION_NAME = RDB$RELATION_FIELDS.RDB$RELATION_NAME
LEFT JOIN RDB$FIELDS ON RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = RDB$FIELDS.RDB$FIELD_NAME
LEFT JOIN RDB$TYPES ON RDB$FIELDS.RDB$FIELD_TYPE = RDB$TYPES.RDB$TYPE

WHERE RDB$RELATIONS.RDB$RELATION_NAME = 'CLIENTES'
AND RDB$RELATIONS.RDB$SYSTEM_FLAG = 0
AND RDB$TYPES.RDB$FIELD_NAME='RDB$FIELD_TYPE'

ORDER BY RDB$RELATION_FIELDS.RDB$FIELD_POSITION

I have a tool to document Firebird databases, maybe it can help you ;) http://www.dbdesc.com

Anyway, if you need more help I'll be glad to help you. Just drop me a line.
JAG Send private email
Tuesday, January 03, 2006
 
 
If you use IBExpert you can turn on the SQL monitor and see what it runs to get table information when you click on a specific table in the treeview. For example, here is the SQL for a table called TBLLOCN10.

select RDB$RELATION_NAME from RDB$RELATIONS
where (RDB$RELATION_NAME = 'TBLLOCN10') and
(RDB$VIEW_BLR is NULL)

select RDB$EXTERNAL_FILE from RDB$RELATIONS
where (RDB$RELATION_NAME = 'TBLLOCN10')

SELECT DISTINCT RDB$FIELD_NAME FROM RDB$DEPENDENCIES
WHERE (RDB$DEPENDED_ON_NAME = 'TBLLOCN10') AND
(NOT (RDB$FIELD_NAME IS NULL)) AND (RDB$DEPENDED_ON_TYPE = 0)

select f.rdb$field_name,
f.rdb$field_source,
f.rdb$null_flag,
f.rdb$default_source,
fs.rdb$null_flag,
fs.rdb$field_name,
fs.rdb$field_type,
fs.rdb$field_length,
fs.rdb$field_scale,
fs.rdb$field_sub_type,
fs.rdb$segment_length,
fs.rdb$dimensions,
d.rdb$dimension,
d.rdb$lower_bound,
d.rdb$upper_bound,
fs.rdb$character_set_id,
f.rdb$collation_id,
cr.rdb$character_set_name,
co.rdb$collation_name,
f.rdb$field_position,
fs.rdb$computed_source,
fs.rdb$character_length,
fs.rdb$default_source,
f.rdb$description,
fs.rdb$collation_id
,fs.rdb$field_precision
from rdb$relation_fields f
left join rdb$fields fs on fs.rdb$field_name = f.rdb$field_source
left join rdb$field_dimensions d on d.rdb$field_name = fs.rdb$field_name
left join rdb$character_sets cr on fs.rdb$character_set_id = cr.rdb$character_set_id
left join rdb$collations co on ((f.rdb$collation_id = co.rdb$collation_id) and
(fs.rdb$character_set_id = co.rdb$character_set_id))
where f.rdb$relation_name = 'TBLLOCN10'
order by f.rdb$field_position, d.rdb$dimension

select rc.rdb$constraint_name,
rc.rdb$relation_name,
i.rdb$field_name,
i.rdb$field_position,
rc.rdb$index_name,
idx.rdb$index_type
from rdb$relation_constraints rc, rdb$index_segments i, rdb$indices idx
where (i.rdb$index_name = rc.rdb$index_name) and
(idx.rdb$index_name = rc.rdb$index_name) and
(rc.rdb$constraint_type = 'PRIMARY KEY')
and (rc.rdb$relation_name = 'TBLLOCN10')
order by rc.rdb$relation_name, i.rdb$field_position

... YOU GET THE POINT...
anon
Tuesday, January 03, 2006
 
 
Excellent.
sharkfish Send private email
Tuesday, January 03, 2006
 
 
Indeed.
V
Friday, January 06, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz