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

Highlighted TextExpand

-- Lista de tablas sin vistas
select * from rdb$relations where rdb$system_flag=0   AND RDB$VIEW_BLR IS NULL

-- Campos de una tabla
SELECT RDB$FIELD_NAME AS field_name,
RDB$FIELD_POSITION AS field_position,
RDB$DESCRIPTION AS field_description,
RDB$DEFAULT_VALUE AS field_default_value,
RDB$NULL_FLAG AS field_not_null_constraint
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = 'VENTAS'

-- InformaciÃ�Â³n detallada sobre los campos de una tabla
SELECT r.RDB$FIELD_NAME AS field_name,
        r.RDB$DESCRIPTION AS field_description,
        r.RDB$DEFAULT_VALUE AS field_default_value,
        r.RDB$NULL_FLAG AS field_not_null_constraint,
        f.RDB$FIELD_LENGTH AS field_length,
        f.RDB$FIELD_PRECISION AS field_precision,
        f.RDB$FIELD_SCALE AS field_scale,
        CASE f.RDB$FIELD_TYPE
          WHEN 261 THEN 'BLOB'
          WHEN 14 THEN 'CHAR'
          WHEN 40 THEN 'CSTRING'
          WHEN 11 THEN 'D_FLOAT'
          WHEN 27 THEN 'DOUBLE'
          WHEN 10 THEN 'FLOAT'
          WHEN 16 THEN 'INT64'
          WHEN 8 THEN 'INTEGER'
          WHEN 9 THEN 'QUAD'
          WHEN 7 THEN 'SMALLINT'
          WHEN 12 THEN 'DATE'
          WHEN 13 THEN 'TIME'
          WHEN 35 THEN 'TIMESTAMP'
          WHEN 37 THEN 'VARCHAR'
          ELSE 'UNKNOWN'
        END AS field_type,
        f.RDB$FIELD_SUB_TYPE AS field_subtype,
        coll.RDB$COLLATION_NAME AS field_collation,
        cset.RDB$CHARACTER_SET_NAME AS field_charset
   FROM RDB$RELATION_FIELDS r
   LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
   LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
   LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
  WHERE r.RDB$RELATION_NAME='VENTAS'  -- table name
ORDER BY r.RDB$FIELD_POSITION;

Report this snippet Tweet

Comment:

You need to login to post a comment.

Posted By

Tagged

Versions (?)

SQLs to obtain metadata about Firebird tables

Related snippets

Choose a language for easy browsing: