como mostrar a qual campo é chave primeria

30/03/2013

1

SELECT DISTINCT R.RDB$FIELD_NAME AS NomeCampo,
CASE R.RDB$NULL_FLAG
WHEN 1 THEN 'SIM'
ELSE 'NAO'
END AS NotNull,
F.RDB$FIELD_LENGTH AS Tamanho,
F.RDB$FIELD_PRECISION AS Precisao,
F.RDB$FIELD_SCALE AS Escala,
CASE F.RDB$FIELD_TYPE
WHEN 7 THEN 'SMALLINT'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 10 THEN 'FLOAT'
WHEN 11 THEN 'D_FLOAT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 14 THEN 'CHAR'
WHEN 16 THEN 'INT64'
WHEN 27 THEN 'DOUBLE'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
WHEN 40 THEN 'CSTRING'
WHEN 261 THEN 'BLOB'
ELSE 'UNKNOWN'
END AS TipoCampo,
F.RDB$FIELD_SUB_TYPE AS SubTipo,
CSET.RDB$CHARACTER_SET_NAME AS Caracter
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 =:wtabela
ORDER BY R.RDB$FIELD_POSITION;

Esse Select funciona muito bem, graças a ajuda de alguns colegas programadores desse forum.
O que eu preciso e não consigo fazer pois nao acho nada no google é o seguinte
Alem dos dados que ele já mostra, quero que aparece a chave primaria igual funciona o esse comando:
CASE R.RDB$NULL_FLAG
WHEN 1 THEN 'SIM'
ELSE 'NAO'
END AS NotNull,

Não achei nenhum comando para me ajudar.

Obrigado
¨Deus Abençoe ¨
Responder

Posts

01/04/2013

Wilton Júnior

alguem?
Responder

01/04/2013

Rafael Cunha

Tenta da seguinte maneira.

SELECT DISTINCT
R.RDB$FIELD_NAME AS NOMECAMPO,
CASE WHEN
  (SELECT S.RDB$FIELD_NAME FROM RDB$RELATION_CONSTRAINTS RC
     LEFT JOIN RDB$INDICES I ON I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME
     LEFT JOIN RDB$INDEX_SEGMENTS S ON S.RDB$INDEX_NAME = I.RDB$INDEX_NAME
   WHERE (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY')
     AND (I.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
     AND (S.RDB$FIELD_NAME = R.RDB$FIELD_NAME))
 IS NOT NULL THEN 'SIM'
ELSE 'NAO'
END AS PRIMARYKEY,
CASE R.RDB$NULL_FLAG
WHEN 1 THEN 'SIM'
ELSE 'NAO'
END AS NOTNULL,
F.RDB$FIELD_LENGTH AS TAMANHO,
F.RDB$FIELD_PRECISION AS PRECISAO,
F.RDB$FIELD_SCALE AS ESCALA,
CASE F.RDB$FIELD_TYPE
WHEN 7 THEN 'SMALLINT'
WHEN 8 THEN 'INTEGER'
WHEN 9 THEN 'QUAD'
WHEN 10 THEN 'FLOAT'
WHEN 11 THEN 'D_FLOAT'
WHEN 12 THEN 'DATE'
WHEN 13 THEN 'TIME'
WHEN 14 THEN 'CHAR'
WHEN 16 THEN 'INT64'
WHEN 27 THEN 'DOUBLE'
WHEN 35 THEN 'TIMESTAMP'
WHEN 37 THEN 'VARCHAR'
WHEN 40 THEN 'CSTRING'
WHEN 261 THEN 'BLOB'
ELSE 'UNKNOWN'
END AS TIPOCAMPO,
F.RDB$FIELD_SUB_TYPE AS SUBTIPO,
CSET.RDB$CHARACTER_SET_NAME AS CARACTER
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 =:WTABELA
ORDER BY R.RDB$FIELD_POSITION;
Responder

03/04/2013

Wilton Júnior

Rafael deu certinho muito obrigo.
Responder