[firebase-br] Fb2.5.2
Marcos Weimer
marcosweimer em gmail.com
Sex Ago 30 08:10:40 -03 2013
Mário, desenvolvi aqui na empresa um replicador de dados específicos e
sofri um bocado para conseguir entender/interpretar as tabelas do sistema,
Aproveito e passo todos que usei logo, pq quando o assunto surge na lista
sempre vem inumeros questionamentos.
Só adaptar.
------------
Tabelas:
SELECT RDB$RELATION_ID as ID, RDB$RELATION_NAME as TABELA
FROM RDB$RELATIONS
WHERE (RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL)
AND (RDB$VIEW_SOURCE IS NULL)
ORDER BY RDB$RELATION_ID
------------
Campos:
SELECT r.RDB$FIELD_NAME AS nome,
r.RDB$DESCRIPTION AS descricao,
f.RDB$FIELD_LENGTH AS tamanho,
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 tipo,
r.rdb$field_position AS posicao,
CASE f.rdb$null_flag WHEN 1 THEN 'S' ELSE case r.rdb$null_flag when 1
then 'S' else 'N' end END AS NOT_NULL,
CASE when f.rdb$computed_source is null then 'N' else 'S' end as
READ_ONLY,
case when ( SELECT idx.RDB$FIELD_NAME as campo
FROM RDB$RELATION_CONSTRAINTS tc
JOIN RDB$INDEX_SEGMENTS idx ON (idx.RDB$INDEX_NAME = tc.RDB$INDEX_NAME)
WHERE tc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.RDB$RELATION_NAME = :tabela
and idx.rdb$field_name = r.rdb$field_name) = r.rdb$field_name then 'S'
else 'N' end as PK
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
WHERE r.RDB$RELATION_NAME= :tabela
ORDER BY r.RDB$FIELD_POSITION
------------
fk...
select rc.rdb$constraint_name as FK_NOME,
rc.rdb$relation_name as FK_TABELA,
(SELECT LIST(trim(fc.rdb$field_name), ',')
from (select seg.rdb$field_name FROM rdb$index_segments seg WHERE
seg.rdb$index_name = rc.rdb$index_name order by seg.rdb$field_position) fc
) AS FK_CAMPO,
ref.rdb$update_rule as FK_UPDATE,
ref.rdb$delete_rule as FK_DELETE,
rcpk.rdb$relation_name as tabela,
(SELECT LIST(trim(c.rdb$field_name), ',')
from ( select seg.rdb$field_name FROM rdb$index_segments seg WHERE
seg.rdb$index_name = rcPK.rdb$index_name order by seg.rdb$field_position) c
) AS campos
from rdb$relation_constraints rc
INNER JOIN rdb$ref_constraints ref on ref.rdb$constraint_name =
rc.rdb$constraint_name
inner join rdb$relation_constraints rcpk on rcpk.rdb$constraint_name =
ref.rdb$const_name_uq
where rc.rdb$relation_name = :tabela
AND RC.rdb$constraint_type = 'FOREIGN KEY'
------------
triggers
select trim(t.rdb$trigger_name) as nome,
case when t.rdb$trigger_inactive = 1 then 'N' else 'S' end as ativa,
t.rdb$trigger_source as source,
t.rdb$trigger_sequence as seq,
t.rdb$trigger_type as tipo
from rdb$triggers t
where t.rdb$trigger_source is not null
and ((t.rdb$system_flag is null) or (t.rdb$system_flag = 0))
and t.rdb$relation_name = :tabela
------------
ck...
select A.RDB$CONSTRAINT_NAME as Nome,
A.RDB$RELATION_NAME as tabela,
C.RDB$TRIGGER_SOURCE as source
from RDB$RELATION_CONSTRAINTS A, RDB$CHECK_CONSTRAINTS B, RDB$TRIGGERS C
where (A.RDB$CONSTRAINT_TYPE = 'CHECK') and
(A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME) and
(B.RDB$TRIGGER_NAME = C.RDB$TRIGGER_NAME) and
(C.RDB$TRIGGER_TYPE = 1)
and (A.RDB$RELATION_NAME = :tabela)
------------
procedures...
select p.rdb$procedure_name as nome, p.rdb$procedure_source as source from
rdb$procedures p
parametros das procedures...
select
case when pp.rdb$parameter_type = 0 then 'E' else 'S' end as IDN_ES,
pp.rdb$parameter_name as nome,
case fs.rdb$field_type
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char'
when 16 then
case fs.rdb$field_sub_type
when 0 then 'bigint'
when 1 then 'numeric'
when 2 then 'decimal'
else 'INT64'
end
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar'
when 261 then 'blob'
else 'UNKNOW'
end as tipo,
fs.rdb$field_sub_type as subtipo,
fs.rdb$segment_length as segmento,
(case when fs.rdb$character_length is null then
case when fs.rdb$field_precision is null then fs.rdb$field_length else
fs.rdb$field_precision end
else
fs.rdb$character_length
end) as tamanho,
(fs.rdb$field_scale * -1) as decimais
from rdb$procedure_parameters pp
left join rdb$fields fs on fs.rdb$field_name = pp.rdb$field_source
where pp.rdb$procedure_name = :nome
and pp.rdb$parameter_type = :idn_ES
order by pp.rdb$parameter_type, pp.rdb$parameter_number
------------
indices...
select
i.rdb$relation_name as tabela,
i.rdb$index_id as seq,
i.rdb$index_name as nome,
case when i.rdb$unique_flag = 0 then 'N' else 'S' end as PK,
case when i.rdb$index_inactive = 0 then 'S' else 'N' end as ATIVO,
i.rdb$foreign_key as nome_fk,
cast((select list(trim(c.rdb$field_name),',')
from rdb$index_segments c
where c.rdb$index_name = i.rdb$index_name
) as varchar(200)) as campos
from rdb$indices i
where i.rdb$system_flag <> 1 --nao pegar os indices da metadata
order by 1,2,3
-=Ma®©oS=-
Marcos R. Weimer
Delphi / C# / ASP.NET / WebServices / Firebird
Em 29 de agosto de 2013 22:14, Mário Reis <secretariadogeral em acra.pt>escreveu:
> Olá minha boa gente,
>
>
>
> Alguém me sabe dizer como fazer para fazer uma query às tabelas de sistema
>
> a saber as trigers "Activa" e "Inactivas" por tabela.
>
> Obrigado
>
>
> ______________________________________________
> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
> Para saber como gerenciar/excluir seu cadastro na lista, use:
> http://www.firebase.com.br/fb/artigo.php?id=1107
> Para consultar mensagens antigas: http://firebase.com.br/pesquisa
>
Mais detalhes sobre a lista de discussão lista