Extraction PK et FK depuis les RDB$

Version imprimable

Bonjour,

Je cherche à obtenir en 1 seule requète le nb de PK et le nb de FK définies sur les tables "utilisateur" d'une DB :roll:

J'ai donc sorti mon guide SQL pour programmer ça:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select a.rdb$relation_name as Tabl, a.rdb$field_id as Nb_col, count(b.RDB$FIELD_NAME) as Nb_PK
from  rdb$index_segments b
inner join rdb$relation_constraints r on b.rdb$index_name=r.rdb$index_name
                                     AND r.rdb$constraint_type='PRIMARY KEY'
inner join rdb$relations a on a.rdb$relation_name=r.rdb$relation_name
where a.RDB$SYSTEM_FLAG=0
union
select c.rdb$relation_name as Tabl, c.rdb$field_id as Nb_col,count(z.RDB$FIELD_NAME) as Nb_FK
from rdb$index_segments z
inner join rdb$relation_constraints k on z.rdb$index_name=k.rdb$index_name
                                     AND k.rdb$constraint_type='FOREIGN KEY'
inner join rdb$relations c on c.rdb$relation_name=k.rdb$relation_name
where c.RDB$SYSTEM_FLAG=0
group by a.rdb$relation_name ,a.rdb$field_id

et devinez...

Citation:

Envoyé par IBexpert

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Et même punition avec le group by embarquant c.rdb$relation_name et c.rdb$field_id en +
Idem en partant de rdb$relations et le 2nd inner sur rdb$index_segments

Par contre

Code:

1
2
3
4
5
6
7
select a.rdb$relation_name as Tabl, a.rdb$field_id as Nb_col, count(b.RDB$FIELD_NAME) as Nb_PK
from rdb$relations a
inner join rdb$relation_constraints r on a.rdb$relation_name=r.rdb$relation_name
                                     AND r.rdb$constraint_type='PRIMARY KEY'
inner join rdb$index_segments b on b.rdb$index_name=r.rdb$index_name
where a.RDB$SYSTEM_FLAG=0
group by a.rdb$relation_name ,a.rdb$field_id

marche correctement, mais je n'ai que les PK...
D'où l'idée du UNION.

Qui peut m'aider ?

09/09/2008, 17h36
SergioMaster

Salut

pour les FK voir mon post et ses suites ici

Salut :D

Il te manque un group by sur le 1° select avant l'union. Non ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT
  A.RDB$RELATION_NAME AS TABL,
  A.RDB$FIELD_ID AS NB_COL,
  COUNT(B.RDB$FIELD_NAME) AS NB_PK
FROM
  RDB$INDEX_SEGMENTS B
  JOIN RDB$RELATION_CONSTRAINTS R
    ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
  JOIN RDB$RELATIONS A
    ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
WHERE
  A.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2
 
UNION
SELECT
  C.RDB$RELATION_NAME AS TABL,
  C.RDB$FIELD_ID AS NB_COL,
  COUNT(Z.RDB$FIELD_NAME) AS NB_FK
FROM
  RDB$INDEX_SEGMENTS Z
INNER JOIN
  RDB$RELATION_CONSTRAINTS K
    ON Z.RDB$INDEX_NAME=K.RDB$INDEX_NAME AND K.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
  JOIN RDB$RELATIONS C
    ON C.RDB$RELATION_NAME=K.RDB$RELATION_NAME
WHERE
  C.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2

Sinon en ce moment je suis sur les CTEs, j'ai ça aussi:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH
  PKS AS (
    SELECT
      A.RDB$RELATION_NAME AS TABL,
      A.RDB$FIELD_ID AS NB_COL,
      COUNT(B.RDB$FIELD_NAME) AS NB_PK,
      LIST(TRIM(B.RDB$FIELD_NAME)) PK_LIST
    FROM
      RDB$INDEX_SEGMENTS B
      JOIN RDB$RELATION_CONSTRAINTS R
        ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
      JOIN RDB$RELATIONS A
        ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
    WHERE
      A.RDB$SYSTEM_FLAG=0
    GROUP BY
      1, 2
  ),
  FKS AS (
    SELECT
      C.RDB$RELATION_NAME AS TABL,
      COUNT(Z.RDB$FIELD_NAME) AS NB_FK,
      LIST(TRIM(Z.RDB$FIELD_NAME)) FK_LIST
    FROM
      RDB$INDEX_SEGMENTS Z
    JOIN RDB$RELATION_CONSTRAINTS K
        ON Z.RDB$INDEX_NAME=K.RDB$INDEX_NAME AND K.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
    JOIN RDB$RELATIONS C
        ON C.RDB$RELATION_NAME=K.RDB$RELATION_NAME
    WHERE
      C.RDB$SYSTEM_FLAG=0
    GROUP BY
      1
  )
SELECT
  PKS.TABL, PKS.NB_COL, PKS.NB_PK, PKS.PK_LIST, FKS.NB_FK, FKS.FK_LIST
FROM
  PKS LEFT JOIN FKS ON PKS.TABL = FKS.TABL

@+ Claudius

09/09/2008, 18h07
qi130

Haaaa !

avec le group by 1,2 la requète passe, mais les FK ne sont pas affichées (pas de colonne Nb_FK dans le grid....
09/09/2008, 18h14
Cl@udius

Comme tu fais un union les champs NB_PK et NB_FK ne font qu'un et porte le nom de NB_PK celui de la 1° requête.
09/09/2008, 18h19
qi130

Alors, retour à la case départ :triste:
09/09/2008, 18h22
Cl@udius

As-tu essayé ma 2° requête avec les CTEs (si tu es sous 2.1) ?

Sinon et comme ça ? C'est fait à l'arrache, surement moyen de faire mieux.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT
  A.RDB$RELATION_NAME AS TABL,
  A.RDB$FIELD_ID AS NB_COL,
  COUNT(B.RDB$FIELD_NAME) AS NB_PK,
  FKS.NB_FK
FROM
  RDB$INDEX_SEGMENTS B
  JOIN RDB$RELATION_CONSTRAINTS R
    ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='PRIMARY KEY'
  JOIN RDB$RELATIONS A
    ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
  LEFT JOIN (
    SELECT
      A.RDB$RELATION_NAME AS TABL,
      COUNT(B.RDB$FIELD_NAME) AS NB_FK
    FROM
      RDB$INDEX_SEGMENTS B
      JOIN RDB$RELATION_CONSTRAINTS R
        ON B.RDB$INDEX_NAME=R.RDB$INDEX_NAME AND R.RDB$CONSTRAINT_TYPE='FOREIGN KEY'
      JOIN RDB$RELATIONS A
        ON A.RDB$RELATION_NAME=R.RDB$RELATION_NAME
    WHERE
      A.RDB$SYSTEM_FLAG=0
    GROUP BY
      1) FKS
   ON A.RDB$RELATION_NAME = FKS.TABL
 
WHERE
  A.RDB$SYSTEM_FLAG=0
GROUP BY
  1, 2, 4;

09/09/2008, 23h05
qi130

Nickel !

:hola:

:merci: à tous.
10/09/2008, 09h40
SergioMaster

@Cl@udius
:oops: désolé mais je ne crois pas que les valeurs retournées soit exacte :bug:, je me retouve avec une table ayant 4 pk et 5 fk alors que celle-ci , je dirais "se trouve en bout de chaine"
1 pk et 4fk
de même j'ai une table temporaire sans index du tout qui se retrouve avec 10 pk

cependant cela me donne une bonne piste de travail , bien que celui-ci soit en cours d'abandon sauf a mes temps perdus
10/09/2008, 09h57
Cl@udius

Salut Sergio :D

Citation:

Envoyé par SergioMaster

:oops: désolé mais je ne crois pas que les valeurs retournées soit exacte :bug:

Ben ça c'est possible

Citation:

Envoyé par Cl@udius

C'est fait à l'arrache, surement moyen de faire mieux.

:mrgreen:

Je vais y jeter un coup d'oeil...

@+ Claudius
10/09/2008, 10h59
qi130

Pour apporter mon grain de sel:

J'ai eu comme Serge des chiffres bizarres, mais en fouillant dans les tables RDB$, j'y ai vu la même chose (genre 4 FK au lieu d'1), et IBexpert rapporte ces 4 FK dans l'onglet DDL (3 fois la même) 8O

Alors j'incrimine autre chose que le script de Cl@udius:
- mon outil case ? (faudra que je regénère le modèle)
- le moteur ? (base créée sous IB6 dont on connait les lacunes)

A la limite, si les chiffres diffèrent entre cette requète et un clone embarquant un distinct, c'est un bon outil de contrôle :yaisse2:

Ca permettra au moins d'éliminer des index qui pénalisent les perfs :king:

Autre point: la requète n'est pas digérée sous Delphi avec un compo FBLDsql (oui je sais, c'est exotique, mais FBLib est utilisable sous D6PE :langue:)

Quelqu'un peut essayer avec un compo + "sérieux" ?
erreur: token unknow SELECT (le 2nd)

Salut

Et avec cette requête, est-ce que les résultats sont plus cohérents ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
  R.RDB$RELATION_NAME TBL_NAME,
  R.RDB$FIELD_ID AS TBL_FLD_COUNT,
  I_S.RDB$INDEX_NAME PK_NAME,
  COUNT(I_S.RDB$FIELD_NAME) PK_FIELD_COUNT,
  FKs.FK_COUNT
FROM
  RDB$RELATIONS R
  JOIN RDB$RELATION_CONSTRAINTS R_C
    ON R.RDB$RELATION_NAME = R_C.RDB$RELATION_NAME
  JOIN RDB$INDEX_SEGMENTS I_S
    ON R_C.RDB$INDEX_NAME = I_S.RDB$INDEX_NAME AND
      R_C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
  LEFT JOIN (
    SELECT
      R.RDB$RELATION_NAME TBL_NAME,
      COUNT(DISTINCT R_C.RDB$CONSTRAINT_NAME) FK_COUNT
    FROM
      RDB$RELATIONS R
      JOIN RDB$RELATION_CONSTRAINTS R_C
        ON R.RDB$RELATION_NAME = R_C.RDB$RELATION_NAME
      JOIN RDB$INDEX_SEGMENTS I_S
        ON R_C.RDB$INDEX_NAME = I_S.RDB$INDEX_NAME AND
          R_C.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
    WHERE
      R.RDB$SYSTEM_FLAG = 0
    GROUP BY
      1
  ) FKs
    ON R.RDB$RELATION_NAME = FKs.TBL_NAME
WHERE
  R.RDB$SYSTEM_FLAG = 0
GROUP BY
  1, 2, 3, 5;

Citation:

Envoyé par qi130

Quelqu'un peut essayer avec un compo + "sérieux" ?
erreur: token unknow SELECT (le 2nd)

Je vais tester avec les UIB.

@+ Claudius

10/09/2008, 11h47
Cl@udius

Citation:

Envoyé par Cl@udius

Je vais tester avec les UIB.

Test done: OK avec la requête ci-dessus.
10/09/2008, 13h18
SergioMaster

:king: Cela me parait plus cohérent
j'ai utilisé Flamerobin pour tester une base un peu , beaucoup, complexe
à priori c'est nickel.

Dés que j'ai un peu de temps je teste avec FIBplus
10/09/2008, 23h02
qi130

Bon en fait, la requète marche aussi avec fblib 8O (je devais pas être ok hier soir :zzz:)

:merci: encore à vous 2