Récupérer le nom des tables ou je peux trouver une valeur de type "clé primaire"

Bonjour
Ma base de données Firebird est composée de quelques 300 tables, (version Firebird 2.5 )

Les quelques 300 tables sont toutes constituées d'une "clé primaire logique", c'est à dire un champ de type Integer rempli à l'aide d'un générateur ou d'une procédure stockée appelée par un applicatif métier (développé en Delphi) qui remplit cette valeur.
Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons et d'afficher le nom de la table dans laquelle se trouve le doublon et la valeur de la "clé logique".
En théorie (mais comme chacun le sait la théorie c'est bien joli), cela n'est pas possible, mais je voudrais tout de même avoir un moyen de vérifier.

Inutile de me faire un cours théorique sur l'intérêt d'une clé primaire physique, je sais parfaitement quels sont les avantages mais aussi les inconvénients, je cherche juste une solution efficace pour retrouver les éventuels doublons (s'il y en a)

Merci pour votre aide.

Bonjour,

pour récupérer les tables contenant des colonnes indexées RDB$INDICES cela me semble un bon point de départ,

Code:

---

1 2	SELECT I.* FROM RDB$INDICES I WHERE I.RDB$UNIQUE_FLAG=1 AND I.RDB$SYSTEM_FLAG=0 and RDB$INDEX_ID=1

---

on peut déjà obtenir les clés unique (I.RDB$INDEX_NAME) j'ai juste un doute sur le fait que RDB$INDEX_ID=1 indique toujours la clé primaire, dans ton cas, RDB$SEGMENT_COUNT=1 pourrait être un critère supplémentaire

Quelque chose comme

Code:

---

1 2 3

SELECT I.RDB$INDEX_NAME,I.RDB$RELATION_NAME,LIST(TRIM(S.RDB$FIELD_NAME)) FROM RDB$INDICES I LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME WHERE I.RDB$UNIQUE_FLAG=0 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL GROUP BY 1,2

---

devrait fournir les clés à tester

une jointure (Full Join) entre ces deux devrait fournir ce qu'il faut pour tester les doublons

Code:

---

1 2 3 4 5 6 7 8 9 10

WITH P AS (SELECT I.RDB$INDEX_NAME ,I.RDB$RELATION_NAME T,LIST(TRIM(S.RDB$FIELD_NAME)) K FROM RDB$INDICES I LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME WHERE I.RDB$UNIQUE_FLAG=1 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL GROUP BY 1,2), S AS (SELECT I.RDB$INDEX_NAME SK,I.RDB$RELATION_NAME ST,LIST(TRIM(S.RDB$FIELD_NAME)) SC FROM RDB$INDICES I LEFT JOIN RDB$INDEX_SEGMENTS S ON I.RDB$INDEX_NAME=S.RDB$INDEX_NAME WHERE I.RDB$UNIQUE_FLAG=0 AND I.RDB$SYSTEM_FLAG=0 AND i.RDB$FOREIGN_KEY IS NULL GROUP BY 1,2)   SELECT P.*,S.* FROM P FULL JOIN S ON P.T=S.ST WHERE P.RDB$INDEX_NAME IS NOT NULL AND S.SK IS NOT NULL

---

si l'on utilise ensuite un EXECUTE STATEMENT à construire à partir de ces résultats en théorie c'est tout bon.

on met tout ça en forme dans un EXECUTE BLOCK car je ne pense pas qu'il faille le mettre dans un procédure et le tour est joué

Voilà en tout cas la théorie à améliorer

Merci pour ces informations.
Mais les champs sont bien indexés mais comme des indexes simples sans aucune contrainte d'unicité, donc la première requête ne me donne rien.

Bonjour,

j'avais cru comprendre par "clé primaire logique" que la colonne incrémentée était déclarée comme primaire :roll: mais surtout unique et déclarée comme index !

Code:

---

1 2	SELECT I.* FROM RDB$INDICES I WHERE I.RDB$SYSTEM_FLAG=0 and RDB$SEGMENT_COUNT=1

---

devrait indiquer les tables avec index ne contenant qu'un seule colonne

il est donc possible pour chaque ligne de cette requête de vérifier s'il y a des colonnes en double ou non
soit par sql soit en tentant de créer un index unique

Salut mballasse.

Citation:

---

Envoyé par mballasse

Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons

---

Qu'est-ce que vous nommez doublon ?
--> Une ligne présente plusieurs fois dans une même table.
--> une ligne présente plusieurs fois dans plusieurs tables.

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.
Savez-vous comment identifier la colonne qui doit être testée ?

Hormis la suppression des doublons, devez-vous faire autre chose de particulier sur ces tables ?

@+

Citation:

---

Envoyé par Artemus24

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.

---

Non, ça c'est la méthode préventive pour éviter la création de doublons, mais si des doublons existent déjà, la création d'un index unique pour les colonnes sera rejetée à cause justement de la présence de ces doublons
La méthode curative dépend de la volumétrie des tables à traiter, quelle est elle ?

Bonjour,

je vous renvoie tous deux au début de la discussion

Citation:

---

Je cherche un moyen de parcourir toutes les tables de ma base à la recherche d'éventuels doublons et d'afficher le nom de la table dans laquelle se trouve le doublon et la valeur de la "clé logique".
En théorie (mais comme chacun le sait la théorie c'est bien joli), cela n'est pas possible, mais je voudrais tout de même avoir un moyen de vérifier.

---

dans ma réponse j'ai indiqué qu'il était possible de retrouver les informations d'index, les tables associées etc... le seul hic j'avais extrapolé en partant sur le fait que cette fameuse clé logique était déjà une contrainte de clé primaire ou, à minima un index unique.

le seul problème qui se pose est en fait d'avoir pour chacune des tables à tester au moins un index unique.
Cela dit rien n'empêche de le faire pour chaque table la création d'une colonne numérique, l'incrémenter, en faire une clé unique puis de faire les vérifications de doublons.

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 39 40 41 42 43

-- OBJECTIF créer une colonne fiable unique index pour travailler ensuite sur le problème des doublons -- écrit à la volée, donc non testé en conditions réelles -- les SUSPEND ne sont là que pour suivre le déroulement, TABLETRAITEE pourrait très bien être une VARIABLE du bloc SET TERM !; -- création de la colonne EXECUTE BLOCK RETURNS (TABLETRAITEE VARCHAR(31)) AS BEGIN FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :TABLETRAITEE DO BEGIN STMT='ALTER TABLE '||:TABLETRAITEE||' ADD VERCOL BIGINT DEFAULT 0 NOT NULL '; EXECUTE STATEMENT STMT; SUSPEND; END END! COMMIT ! -- Mise à jour de la colonne + Création de l'index EXECUTE BLOCK RETURNS ( TABLETRAITEE VARCHAR(31)) AS DECLARE STMT VARCHAR(1024); DECLARE STMTU VARCHAR(1024); DECLARE N BIGINT; DECLARE DBKEY CHAR(8); -- à vérifier, l'utilisation et la taille BEGIN FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :TABLETRAITEE DO BEGIN STMT='SELECT RDB$DB_KEY FROM '||:TABLETRAITEE; STMTU='UPDATE '||:TABLETRAITEE||' SET COLVER=? WHERE RDB$DB_KEY=?'; N=0; FOR EXECUTE STATEMENT STMT INTO :DBKEY DO BEGIN EXCUTE STATEMENT (STMTU) (:N,:DBKEY); -- statement paramétré N=N+1; END STMT='CREATE UNIQUE INDEX '||:TABLETRAITEE||'_CVIDX ON ':TABLETRAITEE; -- un seul truc peut clocher la longueur du nom de l'index ! EXECUTE STATEMENT STMT; SUSPEND; END END! --- Suite recherche des doublons

---

Donc, en théorie (pour reprendre l'expression), c'est possible ! cela prendra du temps ,certainement plus que de vérifier les 300 tables une à une à la main s'il n'y a qu'une seule BDD à traiter mais s'il y en a des dizaines ....

Pour être plus précis sur la structure de ma base de données, je possède quelques 300 tables structurées ainsi :

CREATE TABLE TEST
(
ID INTEGER NOT NULL,,
OUT INTEGER,
REPLIC INTEGER NOT NULL,
CHAMP1 ...
CHAMP2 ...
CHAMP n...
)

CREATE INDEX TESTINDEX 1 ON TEST(ID);

ID : champ "clé logique technique" integer , not null , "normalement" unique pour la base de données, rempli à l'aide d'un générateur directement en SQL ou en passant par une procédure stockée appelée par le programme
OUT : flag integer me disant si l'enregsitrement est supprimé ou pas (aucune suppression physique dans la base)
REPLIC : champ contenant un numéro de version dans la base de données unique et incrémenté à chaque modification me permettant de faire fonctionner un système de réplication de données entre base distantes
champ1 à champN : les champs de ma table

Il n'y a pas d'index unique, pas d'index de clé primaire.

Concernant le nombre d'enregistrements, ça peut varier de quelques dizaines à plus de 4 millions pour la plus grosse table.

Citation:

---

Envoyé par Artemus24

Salut mballasse.


Qu'est-ce que vous nommez doublon ?
--> Une ligne présente plusieurs fois dans une même table.
--> une ligne présente plusieurs fois dans plusieurs tables.

Pour supprimer un doublon dans une table, le mieux est de créer un index unique sur la colonne en question.
Savez-vous comment identifier la colonne qui doit être testée ?

Hormis la suppression des doublons, devez-vous faire autre chose de particulier sur ces tables ?

@+

---

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

La colonne à tester porte toujours le même nom quelque soit la table : ID

Re,

Citation:

---

ID : champ "clé logique technique" integer , not null , "normalement" unique pour la base de données,

---

Citation:

---

Envoyé par mballasse

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

---

c'est donc ID qu'il faut tester pour toutes les tables ! je ne l'avais pas compris ainsi du coup il faut construire en une union entre toutes les tables
(ID,NOMTABLE) puis chercher à l'intérieur de ce résultat s'il existe ID en double

- utiliser une GTT pour récupérer les données

Code:

---

CREATE GLOBAL TEMPORARY TABLE TESTERDOUBLON (ID INTEGER,NOMTABLE VARCHAR(31));

---

-puis

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SET TERM !; EXECUTE BLOCK RETURNS( ID INTEGER, NOMTABLE VARCHAR(31)) AS DECLARE VARIABLE N INTEGER; DECLARE VARIABLE T VARCHAR(31); DECLARE STMT VARCHAR(1024); BEGIN N=0; FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :T DO BEGIN STMT='SELECT ID FROM '||:T; FOR EXECUTE STATEMENT STMT INTO :N DO INSERT INTO TESTERDOUBLON(ID,NOMTABLE) VALUES (:N,:T); END -- Doublons FOR WITH D AS (SELECT COUNT(lD),ID FROM TESTER DOUBLON GROUP BY ID HAVING COUNT(ID) > 1 SELECT TD.ID,TD.NOMTABLE FROM TESTERDOUBLON TD JOIN D ON TD.ID=D.ID INTO :ID,:NOMTABLE DO SUSPEND;   END!

---

Bon, encore une fois, écrit sans trop de contrôles et donc à améliorer

Salut à tous.

Citation:

---

Envoyé par SergioMaster

le seul problème qui se pose est en fait d'avoir pour chacune des tables à tester au moins un index unique.

---

Oui, sinon comme identifier d'une manière unique les lignes en doublons.

Citation:

---

Envoyé par SergioMaster

Cela dit rien n'empêche de le faire pour chaque table la création d'une colonne numérique, l'incrémenter, en faire une clé unique puis de faire les vérifications de doublons.

---

Je pense que la règle imposée par mballasse est de ne pas modifier la base de données. A lui de confirmer que c'est bien le cas.

Citation:

---

Envoyé par mballasse

Pour être plus précis sur la structure de ma base de données, je possède quelques 300 tables structurées ainsi :

---

Voulez-vous dire que vos 300 tables ont toutes la même structure et les mêmes noms de colonnes ?

Citation:

---

Envoyé par mballasse

Il n'y a pas d'index unique, pas d'index de clé primaire.

---

Oups !!! Bonjour la performance.

Citation:

---

Envoyé par mballasse

Un doublon est une valeur qui peut être en double dans la même table (ça c'est facile à retrouver) mais elle pourrait éventuellement être présente dans une autre table

---

C'est bien ce qui me semblait. Par doublon, vous avez les deux notions, à savoir dans une table et inter-table, si je peux m'exprimer ainsi.

La solution est en fait de fusionner toutes vos tables en une seule, afin d'identifier d'une manière simple vos doublons.

Citation:

---

Envoyé par mballasse

La colonne à tester porte toujours le même nom quelque soit la table : ID

---

Il vaut mieux, sinon cela va compliquer la requête.

J'ai repris l'excellente analyse de SergioMaster, et j'ai créé un jeu d'essai, histoire de voire ce que cela donne à l'exécution.
pour la colonne 'id', j'ai mis une chaîne de caractères.

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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203

CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;   -- ============================ -- Création de la table 'test1' -- ============================   create table test1 ( id char(20) not null );   -- ====================== -- Insertion dans 'test1' -- ======================   insert into test1 (id) values ('un'); insert into test1 (id) values ('cinq'); insert into test1 (id) values ('huit'); insert into test1 (id) values ('dix'); insert into test1 (id) values ('quinze'); insert into test1 (id) values ('vingt-trois'); insert into test1 (id) values ('dix'); insert into test1 (id) values ('trente-deux');   -- ================= -- Vidage de 'test1' -- =================   select * from test1;   ID ==================== un cinq huit dix quinze vingt-trois dix trente-deux     -- ============================ -- Création de la table 'test2' -- ============================   create table test2 ( id char(20) not null );   -- ====================== -- Insertion dans 'test2' -- ======================   insert into test2 (id) values ('deux'); insert into test2 (id) values ('trois'); insert into test2 (id) values ('cinq'); insert into test2 (id) values ('neuf'); insert into test2 (id) values ('onze'); insert into test2 (id) values ('quinze'); insert into test2 (id) values ('dix-huit'); insert into test2 (id) values ('neuf');   -- ================= -- Vidage de 'test2' -- =================   select * from test2;   ID ==================== deux trois cinq neuf onze quinze dix-huit neuf     -- ============================ -- Création de la table 'test3' -- ============================   create table test3 ( id char(20) not null );   -- ====================== -- Insertion dans 'test3' -- ======================   insert into test3 (id) values ('quatre'); insert into test3 (id) values ('huit'); insert into test3 (id) values ('douze'); insert into test3 (id) values ('trente-trois'); insert into test3 (id) values ('douze'); insert into test3 (id) values ('quarente-deux'); insert into test3 (id) values ('cinquante-trois'); insert into test3 (id) values ('cent-vingt-huit');   -- ================= -- Vidage de 'test3' -- =================   select * from test3;   ID ==================== quatre huit douze trente-trois douze quarente-deux cinquante-trois cent-vingt-huit     commit;   -- =========== -- Requête N°1 -- ===========   select trim(rdb$relation_name) as name from rdb$relations where rdb$system_flag = 0;   NAME =============================== TEST1 TEST2 TEST3     -- =========== -- Requête N°3 -- ===========   CREATE GLOBAL TEMPORARY TABLE doublon (lieu char(20), id char(20));   -- =========== -- Requête N°2 -- ===========   SET TERM #;   execute block returns (lieu char(20), id char(20)) as declare variable name varchar(30); declare variable stmt varchar(4000) default ''; begin for select trim(rdb$relation_name) from rdb$relations where rdb$system_flag = 0 into :name do begin stmt = 'select id from ' || :name;   for execute statement stmt into :id do insert into doublon (lieu, id) values (:name, :id); end   for with cte AS ( select id, count(id) as nbre from doublon group by id having count(id) > 1 ) select t1.lieu, t1.id from doublon as t1 join cte as t2 on t2.id = t1.id order by id into :lieu, :id do suspend;   end#   LIEU ID ==================== ==================== TEST1 cinq TEST2 cinq TEST1 dix TEST1 dix TEST3 douze TEST3 douze TEST1 huit TEST3 huit TEST2 neuf TEST2 neuf TEST1 quinze TEST2 quinze     SET TERM ;#   exit;   Appuyez sur une touche pour continuer...

---

Avant de reprendre l'exemple de SergioMaster, j'ai fait une tentative, sans créer une table temporaire, mais en passant par un "union all".
Or je ne sais pas pourquoi, tous les doublons dans une table donnée, ont été supprimés.

@+

Merci pour vos réponses à tous
Pour compléter la remarque, il y a bien un index sur la colonne de chaque table mais il n'y a pas la contrainte unique
Les tables n'ont évidemment toutes la même structure, à un détail près, c'est qu'elles ont toutes les 3 champs techniques précisés ci dessus (ID, OUT, REPLIC)
je ne vais bien évidemment pas modifier ma base de données, il me faut "idéalement " une routine Live !!!!

Bonjour,

Citation:

---

je ne vais bien évidemment pas modifier ma base de données, il me faut "idéalement " une routine Live !

---

je ne pense pas que créer une GTT soit un entorse énorme puisque ce n'est qu'un création de table destructible par la suite. je ne sais pas si cette GTT peut se créer dans l'EXECUTE BLOCK (enfin on peut certainement la créer mais sera-t-elle utilisable ?)

un script SQL plus complet devrait être ce que tu nommes "routine live"

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

CREATE GLOBAL TEMPORARY TABLE TESTERDOUBLON (ID INTEGER,NOMTABLE VARCHAR(31)); COMMIT; SET TERM !; EXECUTE BLOCK RETURNS( ID INTEGER, NOMTABLE VARCHAR(31)) AS DECLARE VARIABLE N INTEGER; DECLARE VARIABLE T VARCHAR(31); DECLARE STMT VARCHAR(1024); BEGIN N=0; FOR SELECT TRIM(RDB$RELATION_NAME) FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG=0 AND RDB$RELATION_TYPE=0 INTO :T DO BEGIN STMT='SELECT ID FROM '||:T; FOR EXECUTE STATEMENT STMT INTO :N DO INSERT INTO TESTERDOUBLON(ID,NOMTABLE) VALUES (:N,:T); END -- Doublons FOR WITH D AS (SELECT COUNT(lD),ID FROM TESTER DOUBLON GROUP BY ID HAVING COUNT(ID) > 1 SELECT TD.ID,TD.NOMTABLE FROM TESTERDOUBLON TD JOIN D ON TD.ID=D.ID INTO :ID,:NOMTABLE DO SUSPEND; END! SET TERM ; ! DROP TABLE TESTERDOUBLON;

---

Merci a vous tous, je teste cela au plus vite et je vous fais un retour