Posted By

agarcim on 12/23/12


Tagged

metadata SQLFirebird


Versions (?)

SQLs to obtain metadata about Firebird tables


 / Published in: SQL
 

URL: http://www.alberton.info/firebird_sql_meta_info.html

Obtains metadata of Firebird tables

  1. -- Lista de tablas sin vistas
  2. SELECT * FROM rdb$relations WHERE rdb$system_flag=0 AND RDB$VIEW_BLR IS NULL
  3.  
  4. -- Campos de una tabla
  5. SELECT RDB$FIELD_NAME AS field_name,
  6. RDB$FIELD_POSITION AS field_position,
  7. RDB$DESCRIPTION AS field_description,
  8. RDB$DEFAULT_VALUE AS field_default_value,
  9. RDB$NULL_FLAG AS field_not_null_constraint
  10. FROM RDB$RELATION_FIELDS
  11. WHERE RDB$RELATION_NAME = 'VENTAS'
  12.  
  13. -- Informaci�³n detallada sobre los campos de una tabla
  14. SELECT r.RDB$FIELD_NAME AS field_name,
  15. r.RDB$DESCRIPTION AS field_description,
  16. r.RDB$DEFAULT_VALUE AS field_default_value,
  17. r.RDB$NULL_FLAG AS field_not_null_constraint,
  18. f.RDB$FIELD_LENGTH AS field_length,
  19. f.RDB$FIELD_PRECISION AS field_precision,
  20. f.RDB$FIELD_SCALE AS field_scale,
  21. CASE f.RDB$FIELD_TYPE
  22. WHEN 261 THEN 'BLOB'
  23. WHEN 14 THEN 'CHAR'
  24. WHEN 40 THEN 'CSTRING'
  25. WHEN 11 THEN 'D_FLOAT'
  26. WHEN 27 THEN 'DOUBLE'
  27. WHEN 10 THEN 'FLOAT'
  28. WHEN 16 THEN 'INT64'
  29. WHEN 8 THEN 'INTEGER'
  30. WHEN 9 THEN 'QUAD'
  31. WHEN 7 THEN 'SMALLINT'
  32. WHEN 12 THEN 'DATE'
  33. WHEN 13 THEN 'TIME'
  34. WHEN 35 THEN 'TIMESTAMP'
  35. WHEN 37 THEN 'VARCHAR'
  36. ELSE 'UNKNOWN'
  37. END AS field_type,
  38. f.RDB$FIELD_SUB_TYPE AS field_subtype,
  39. coll.RDB$COLLATION_NAME AS field_collation,
  40. cset.RDB$CHARACTER_SET_NAME AS field_charset
  41. FROM RDB$RELATION_FIELDS r
  42. LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
  43. LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
  44. LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
  45. WHERE r.RDB$RELATION_NAME='VENTAS' -- table name
  46. ORDER BY r.RDB$FIELD_POSITION;

Report this snippet  

You need to login to post a comment.