Extraction PK et FK depuis les RDB$

**qi130** · 09/09/2008, 15h08

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

**SergioMaster** · 09/09/2008, 16h36

Salut

pour les FK voir mon post et ses suites ici

**Cl@udius** · 09/09/2008, 16h38

Salut

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**qi130** · 09/09/2008, 17h07

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....

**Cl@udius** · 09/09/2008, 17h14

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.

**qi130** · 09/09/2008, 17h19

Alors, retour à la case départ

**Cl@udius** · 09/09/2008, 17h22

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

**Cl@udius** · 09/09/2008, 17h55

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

@+

**qi130** · 09/09/2008, 22h05

Nickel !

à tous.

**SergioMaster** · 10/09/2008, 08h40

@Cl@udius

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

, 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

**Cl@udius** · 10/09/2008, 08h57

Salut Sergio

Envoyé par SergioMaster

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

Ben ça c'est possible

Envoyé par Cl@udius

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

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

@+ Claudius

**qi130** · 10/09/2008, 09h59

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)

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

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

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

)

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

**Cl@udius** · 10/09/2008, 10h28

Salut

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

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

**Cl@udius** · 10/09/2008, 10h47

Envoyé par Cl@udius

Je vais tester avec les UIB.

Test done: OK avec la requête ci-dessus.

**SergioMaster** · 10/09/2008, 12h18

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

**qi130** · 10/09/2008, 22h02

Bon en fait, la requète marche aussi avec fblib

(je devais pas être ok hier soir

)

encore à vous 2

Extraction PK et FK depuis les RDB$

SQL Firebird

Discussions similaires

Partager

Partager