Listar as [Tabelas, Views] em Firebird

Listar as [Tabelas, Views] em Firebird

  novembro 17, 2012    Anônimo

---

Listar todos DOMAIN

select F.*,
       (select C.RDB$CHARACTER_SET_NAME
        from RDB$CHARACTER_SETS C
        where C.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID) RDB$CHARACTER_SET_NAME,
       (select C1.RDB$COLLATION_NAME
        from RDB$COLLATIONS C1
        where C1.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID
              and C1.RDB$COLLATION_ID = F.RDB$COLLATION_ID) RDB$COLLATION_NAME
from RDB$FIELDS F
where (F.RDB$SYSTEM_FLAG <> 1 or F.RDB$SYSTEM_FLAG is null)
      and (F.RDB$FIELD_NAME not like 'RDB$%')
order by F.RDB$FIELD_NAME  

Listar todas FUNCOES

select *
from RDB$FUNCTIONS
where (RDB$SYSTEM_FLAG <> 1 or RDB$SYSTEM_FLAG is null)
order by RDB$FUNCTION_NAME 

Listar todos PRIMARYKEY

select C.RDB$CONSTRAINT_NAME, C.RDB$CONSTRAINT_TYPE, C.RDB$RELATION_NAME FK_RELATION_NAME,
       C.RDB$INDEX_NAME FK_INDEX_NAME, I2.RDB$RELATION_NAME PK_RELATION_NAME, I2.RDB$INDEX_NAME PK_INDEX_NAME,
       I1.RDB$INDEX_TYPE FK_INDEX_TYPE, I2.RDB$INDEX_TYPE PK_INDEX_TYPE
from RDB$RELATION_CONSTRAINTS C, RDB$REF_CONSTRAINTS R, RDB$INDICES I1, RDB$INDICES I2
where C.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
      and I1.RDB$INDEX_NAME = C.RDB$INDEX_NAME
      and I2.RDB$INDEX_NAME = I1.RDB$FOREIGN_KEY
      and R.RDB$CONSTRAINT_NAME = C.RDB$CONSTRAINT_NAME
      and C.RDB$CONSTRAINT_NAME not like 'IBE$%'   

Listar todas TABELAS

select RDB$RELATION_NAME, RDB$DESCRIPTION, RDB$EXTERNAL_FILE, RDB$SYSTEM_FLAG, RDB$VIEW_BLR
from RDB$RELATIONS
where (RDB$SYSTEM_FLAG <> 1 or RDB$SYSTEM_FLAG is null)
      and RDB$VIEW_BLR is null
order by RDB$RELATION_NAME  

Listar todas VIEW

select R.RDB$RELATION_NAME, R.RDB$VIEW_SOURCE, R.RDB$DESCRIPTION
from RDB$RELATIONS R
where (R.RDB$SYSTEM_FLAG <> 1 or R.RDB$SYSTEM_FLAG is null)
      and not R.RDB$VIEW_BLR is null
      and R.RDB$FLAGS = 1
order by R.RDB$RELATION_NAME  

Listar todas PROCEDURES com os fontes

select RDB$PROCEDURE_NAME, RDB$SYSTEM_FLAG, RDB$DESCRIPTION, RDB$PROCEDURE_SOURCE, RDB$PROCEDURE_BLR
from RDB$PROCEDURES
where (RDB$SYSTEM_FLAG <> 1 or RDB$SYSTEM_FLAG is null)
      and RDB$PROCEDURE_NAME not like 'IBE$%'
order by RDB$PROCEDURE_NAME   

Listar todas TRIGGER com os fontes

select T.RDB$TRIGGER_NAME, T.RDB$RELATION_NAME, T.RDB$TRIGGER_SOURCE, T.RDB$DESCRIPTION, T.RDB$TRIGGER_TYPE,
       T.RDB$TRIGGER_BLR, T.RDB$TRIGGER_SEQUENCE, T.RDB$TRIGGER_INACTIVE, T.RDB$SYSTEM_FLAG
from RDB$TRIGGERS T
join RDB$RELATIONS R on R.RDB$RELATION_NAME = T.RDB$RELATION_NAME
where (R.RDB$SYSTEM_FLAG <> 1 or R.RDB$SYSTEM_FLAG is null)
      and not exists(select *
                     from RDB$CHECK_CONSTRAINTS CHK
                     where T.RDB$TRIGGER_NAME = CHK.RDB$TRIGGER_NAME)
      and not(R.RDB$VIEW_BLR is not null
      and T.RDB$TRIGGER_NAME like 'CHECK_%'
      and T.RDB$TRIGGER_SOURCE is null)
order by T.RDB$TRIGGER_NAME  

Listar todos CAMPOS DA TABELA com detalhes

select R.RDB$FIELD_NAME, R.RDB$FIELD_POSITION, R.RDB$DEFAULT_VALUE, R.RDB$DEFAULT_SOURCE, R.RDB$NULL_FLAG,
       R.RDB$COLLATION_ID, F.RDB$CHARACTER_SET_ID F_CHARACTER_SET_ID, R.RDB$DESCRIPTION, F.RDB$FIELD_NAME F_FLD_NAME,
       F.RDB$FIELD_TYPE, F.RDB$FIELD_LENGTH F_FLD_LENGTH, F.RDB$CHARACTER_LENGTH F_CHR_LENGTH,
       F.RDB$FIELD_SUB_TYPE F_FLD_SUB_TYPE, F.RDB$SEGMENT_LENGTH F_SEGMENT_LENGTH,
       F.RDB$VALIDATION_SOURCE F_VALIDATION_SOURCE, F.RDB$DEFAULT_VALUE F_DEFAULT_VALUE,
       F.RDB$DEFAULT_SOURCE F_DEFAULT_SOURCE, F.RDB$COMPUTED_SOURCE, F.RDB$FIELD_SCALE F_FIELD_SCALE, F.RDB$DIMENSIONS,
       (select C1.RDB$COLLATION_NAME
        from RDB$COLLATIONS C1
        where C1.RDB$COLLATION_ID = R.RDB$COLLATION_ID
              and C1.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID) RDB$COLLATION_NAME,
       (select C2.RDB$CHARACTER_SET_NAME
        from RDB$CHARACTER_SETS C2
        where C2.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID) RDB$CHARACTER_SET_NAME
from RDB$RELATION_FIELDS R, RDB$FIELDS F
where R.RDB$RELATION_NAME = :NAME
      and F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE
order by R.RDB$FIELD_POSITION, R.RDB$FIELD_NAME