[firebase-br] Checar Estrutura da Tabela

[Rodrigo Teodoro]

Isto no Firebird 2.0 ok!

CREATE OR ALTER PROCEDURE BUSCADESCRICAO (
     p_tabela varchar(31))
returns (
     o_reservado char(1),
     o_campo varchar(31),
     o_tipo varchar(31),
     o_indice varchar(31),
     o_tamanho integer,
     o_valorpadrao varchar(31),
     o_precisao integer,
     o_nulabilidade varchar(1),
     o_descricao varchar(255))
as
BEGIN
    FOR
         SELECT DISTINCT TRIM(CAMPOS.RDB$FIELD_NAME) AS CAMPO,
         --TIPO DO CAMPO
         TRIM(CASE
             WHEN DADOSCAMPO.RDB$FIELD_PRECISION > 0 THEN 'NUMERIC'
             WHEN TIPOS.RDB$TYPE_NAME = 'LONG' THEN 'INTEGER'
             WHEN TIPOS.RDB$TYPE_NAME = 'SHORT' THEN 'SMALLINT'
             WHEN TIPOS.RDB$TYPE_NAME = 'INT64' THEN 'NUMERIC'
             WHEN TIPOS.RDB$TYPE_NAME = 'VARYING' THEN 'VARCHAR'
             WHEN TIPOS.RDB$TYPE_NAME = 'TEXT' THEN 'CHAR'
             WHEN TIPOS.RDB$TYPE_NAME = 'BLOB' THEN 'BLOB SUB_TYPE 1'
             ELSE TIPOS.RDB$TYPE_NAME
         END) AS TIPO,
         TRIM(COALESCE((SELECT
             C.RDB$CONSTRAINT_TYPE
             FROM
               RDB$RELATION_CONSTRAINTS C,
               RDB$INDEX_SEGMENTS S
             WHERE
               C.RDB$RELATION_NAME = CAMPOS.RDB$RELATION_NAME  AND
               S.RDB$FIELD_NAME    = CAMPOS.RDB$FIELD_NAME     AND
               S.RDB$INDEX_NAME = C.RDB$INDEX_NAME),'CAMPO')) AS INDICE,

         --TAMANHO DO CAMPO
        TRIM(CASE
             WHEN DADOSCAMPO.RDB$FIELD_TYPE IN(16,8) THEN 
DADOSCAMPO.RDB$FIELD_PRECISION
             ELSE DADOSCAMPO.RDB$FIELD_LENGTH
         END) AS TAMANHOCAMPO,

         --VALOR PADRAO
         TRIM(SUBSTRING(CAMPOS.RDB$DEFAULT_SOURCE FROM 8 FOR 
(CHAR_LENGTH(CAMPOS.RDB$DEFAULT_SOURCE) - 7))) AS VALORPADRAO,

         --PRECISAO
         (DADOSCAMPO.RDB$FIELD_SCALE*-1) AS PRECISAO,
         --SE O CAMPO E NULO
         CASE
             CAMPOS.RDB$NULL_FLAG WHEN 1 THEN 'N'
             ELSE 'Y'
         END AS NULABILIDADE,
         --DESCRICAO
         TRIM(CAMPOS.RDB$DESCRIPTION) AS DESCRICAO,
         CASE WHEN TRIM(CAMPOS.RDB$FIELD_NAME) = 'DOMAIN' THEN
                 'S'
              WHEN TRIM(CAMPOS.RDB$FIELD_NAME) = 'BLOCK' THEN
                 'S'
              ELSE
                 'N'
         END AS RESERVADO
         FROM
             RDB$RELATIONS TABELAS,
             RDB$RELATION_FIELDS CAMPOS,
             RDB$FIELDS DADOSCAMPO,
             RDB$TYPES TIPOS
         WHERE
             TABELAS.RDB$RELATION_NAME = :P_TABELA AND
             TIPOS.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' AND
             TABELAS.RDB$RELATION_NAME = CAMPOS.RDB$RELATION_NAME AND
             CAMPOS.RDB$FIELD_SOURCE = DADOSCAMPO.RDB$FIELD_NAME AND
             DADOSCAMPO.RDB$FIELD_TYPE = TIPOS.RDB$TYPE
         ORDER BY
             CAMPOS.RDB$FIELD_POSITION
         INTO
             :O_CAMPO,
             :O_TIPO,
             :O_INDICE,
             :O_TAMANHO,
             :O_VALORPADRAO,
             :O_PRECISAO,
             :O_NULABILIDADE,
             :O_DESCRICAO,
             :O_RESERVADO
     DO BEGIN
         SUSPEND;
     END
END