Fórum Problema com SQL de Metadados #470887
21/02/2014
0
Estou fazendo um SQL para validar coisas do meu DB em Delphi mais estou com um problema de resultado
O SQL Abaixo traz o nome da tabela no DB, nome do Campo e suas propriedades tipo, Se é chave primaria, secundaria, quais as tabelas relacionadas as chaves secundarias e etc.
Se rodarem o SQL abaixo vai trazer tudo quase certo, pois quando o campo no DB é CHAVE primária está repetindo o campo 2X, alguém poderia dar uma olhada para me ajudar ?
SELECT DISTINCT (CAST(r.RDB$RELATION_NAME AS VARCHAR(70)))table_name, (r.RDB$FIELD_NAME)field_name,
IIF(COALESCE(r.RDB$NULL_FLAG, 0) = 0, Null, 'NOT NULL')FIELD_NULL,
(f.RDB$FIELD_LENGTH)field_length,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, f.RDB$FIELD_PRECISION)field_precision,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, (f.RDB$FIELD_SCALE * -1))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)field_type,
(rc.RDB$CONSTRAINT_TYPE)constraint_type,
(s2.RDB$FIELD_NAME)references_field,
(i2.RDB$RELATION_NAME)references_table,
IIF(r.RDB$DEFAULT_VALUE is NULL, 'NULL', CAST(r.RDB$DEFAULT_VALUE AS VARCHAR(200))) AS field_default_value
FROM RDB$FIELDS f
LEFT JOIN RDB$RELATION_FIELDS r 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
LEFT JOIN RDB$INDICES i ON i.RDB$RELATION_NAME = r.RDB$RELATION_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s ON s.RDB$FIELD_NAME = r.RDB$FIELD_NAME AND s.RDB$INDEX_NAME = i.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE Not(r.RDB$RELATION_NAME like('VW_%')) and Not(r.RDB$RELATION_NAME like('%$%')) AND
(NOT (r.RDB$NULL_FLAG IS NULL) OR (NOT (rc.RDB$CONSTRAINT_TYPE IS NULL)))
ORDER BY table_name, constraint_type DESC, field_name
O SQL Abaixo traz o nome da tabela no DB, nome do Campo e suas propriedades tipo, Se é chave primaria, secundaria, quais as tabelas relacionadas as chaves secundarias e etc.
Se rodarem o SQL abaixo vai trazer tudo quase certo, pois quando o campo no DB é CHAVE primária está repetindo o campo 2X, alguém poderia dar uma olhada para me ajudar ?
SELECT DISTINCT (CAST(r.RDB$RELATION_NAME AS VARCHAR(70)))table_name, (r.RDB$FIELD_NAME)field_name,
IIF(COALESCE(r.RDB$NULL_FLAG, 0) = 0, Null, 'NOT NULL')FIELD_NULL,
(f.RDB$FIELD_LENGTH)field_length,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, f.RDB$FIELD_PRECISION)field_precision,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, (f.RDB$FIELD_SCALE * -1))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)field_type,
(rc.RDB$CONSTRAINT_TYPE)constraint_type,
(s2.RDB$FIELD_NAME)references_field,
(i2.RDB$RELATION_NAME)references_table,
IIF(r.RDB$DEFAULT_VALUE is NULL, 'NULL', CAST(r.RDB$DEFAULT_VALUE AS VARCHAR(200))) AS field_default_value
FROM RDB$FIELDS f
LEFT JOIN RDB$RELATION_FIELDS r 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
LEFT JOIN RDB$INDICES i ON i.RDB$RELATION_NAME = r.RDB$RELATION_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s ON s.RDB$FIELD_NAME = r.RDB$FIELD_NAME AND s.RDB$INDEX_NAME = i.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE Not(r.RDB$RELATION_NAME like('VW_%')) and Not(r.RDB$RELATION_NAME like('%$%')) AND
(NOT (r.RDB$NULL_FLAG IS NULL) OR (NOT (rc.RDB$CONSTRAINT_TYPE IS NULL)))
ORDER BY table_name, constraint_type DESC, field_name
Xyberx
Curtir tópico
+ 0
Responder
Posts
22/02/2014
Alex Lekao
Oi Bom dia!!!
Nao entendi muito bem, e acredito que minha experiencia nao seja tamanha assim, mas vamos la... rsrsr
Ja experimentou colocar um group by para ver se resolve?
Uma alternativa talvez seja um subselect com top 1 por exemplo para esse caso, se eles forem sempre iguais, acredito que tambem resolva.
Bom espero ter ajudado.
Abraco.
Alex - Lekao
Nao entendi muito bem, e acredito que minha experiencia nao seja tamanha assim, mas vamos la... rsrsr
Ja experimentou colocar um group by para ver se resolve?
Uma alternativa talvez seja um subselect com top 1 por exemplo para esse caso, se eles forem sempre iguais, acredito que tambem resolva.
Bom espero ter ajudado.
Abraco.
Alex - Lekao
Responder
Gostei + 0
08/05/2014
Emerson Nascimento
sei que a questão já é antiga, mas como não houve indicação de solução....
SELECT
CAST(r.RDB$RELATION_NAME AS VARCHAR(70)) table_name,
r.RDB$FIELD_POSITION,
r.RDB$FIELD_NAME field_name,
IIF(COALESCE(r.RDB$NULL_FLAG, 0) = 0, Null, 'NOT NULL') FIELD_NULL,
(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) field_type,
(f.RDB$FIELD_LENGTH) field_length,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, f.RDB$FIELD_PRECISION) field_precision,
IIF((f.RDB$FIELD_PRECISION is null) or (f.RDB$FIELD_PRECISION = 0), Null, (f.RDB$FIELD_SCALE * -1)) field_scale,
MAX((CASE WHEN rc.RDB$CONSTRAINT_TYPE = 'UNIQUE' THEN 'X' ELSE ' ' END)) UNIQUE_KEY,
MAX((CASE WHEN rc.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'X' ELSE ' ' END)) PRIMARY_KEY,
MAX((CASE WHEN rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'X' ELSE ' ' END)) FOREIGN_KEY,
IIF(r.RDB$DEFAULT_VALUE is NULL, Null, CAST(r.RDB$DEFAULT_VALUE AS VARCHAR(200))) AS field_default_value,
max(s2.RDB$FIELD_NAME) references_field,
max(i2.RDB$RELATION_NAME) references_table
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 AND f.RDB$CHARACTER_SET_ID = coll.RDB$CHARACTER_SET_ID
LEFT JOIN RDB$INDICES i ON i.RDB$RELATION_NAME = r.RDB$RELATION_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s ON s.RDB$FIELD_NAME = r.RDB$FIELD_NAME AND s.RDB$INDEX_NAME = i.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc2 ON rc2.RDB$CONSTRAINT_NAME = refc.RDB$CONST_NAME_UQ
LEFT JOIN RDB$INDICES i2 ON i2.RDB$INDEX_NAME = rc2.RDB$INDEX_NAME
LEFT JOIN RDB$INDEX_SEGMENTS s2 ON i2.RDB$INDEX_NAME = s2.RDB$INDEX_NAME
WHERE Not(r.RDB$RELATION_NAME like('VW_%')) and Not(r.RDB$RELATION_NAME like('%$%'))
group by
CAST(r.RDB$RELATION_NAME AS VARCHAR(70)),
r.RDB$FIELD_NAME,f.RDB$FIELD_TYPE,r.RDB$NULL_FLAG,f.RDB$FIELD_LENGTH,
f.RDB$FIELD_PRECISION,f.RDB$FIELD_SCALE, r.RDB$FIELD_POSITION,r.RDB$DEFAULT_VALUE
ORDER BY
table_name, r.RDB$FIELD_POSITION
Responder
Gostei + 0
Clique aqui para fazer login e interagir na Comunidade :)