Plus de MCD, plus de MPD... : comment trouver la colonne et la table où est stocké un libellé? - Find the column of this label
Introduction
Il arrive que dans un SI les développeurs ne sachent plus associer un écran d'une application à une table de la base : application d'un éditeur tiers ayant fait faillite donc accès au code source impossible, crash du disque dur contenant la documentation, départ des développeurs experts sans passage de témoin, etc etc...
On va voir alors comment, pour un écran et un champ précis, retrouver la fameuse table en nous aidant du dictionnaire de données d'Oracle.
Points d'attention
Aucun.
Base de tests
Une base Oracle 12.
Exemples
J'utilise le schéma HR d'Oracle, livré en standard sur toute base de test dont voici les tables de ma base.
SQL> select * from cat where table_type = 'TABLE' order by table_name;
TABLE_NAME TABLE_TYPE
------------------------------------------------------------------------- ---------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
LOG_SPEED TABLE
REGIONS TABLE
8 rows selected.
Le problème est donc le suivant : nous avons un écran où le libellé TEXAS apparaît et nous voulons savoir à quelle table et à quelle colonne ce champ est rattaché. Les indices sont : un libellé de type chaîne de caractère et, selon le client, le user propriétaire des données applicatives est HR.
On construit donc un ordre SQL pour générer des SELECT concernant les colonnes et tables du user HR correspondant à ces critères. Le résultat va dans un fichier sql qu'on va exécuter ensuite.
On peut aussi filtrer sur d'autres users et ajouter CHAR pour le type de la colonne. Il est possible aussi, via DBA_TAB_COLS, d'utiliser la colonne DATA_LENGTH pour limiter les colonnes à sélectionner. Par exemple, on remplit le champ à son maximum avec un libellé genre 'ABCD...', on compte le nombre de caractères et on filtre ensuite sur la taille max de ce champ pour récupérer moins de colonnes.
Attention à bien filtrer sur les objets de type TABLE car dans DBA_TAB_COLS, nous avons à la fois les colonnes des tables MAIS aussi les colonnes des vues, contrairement à ce qu'indique le nom de l'objet.
SQL> spool search.sql
SQL> select
'SELECT count(*),
''' || T.table_name || ''',
''' || T.column_name || '''' ||
' FROM ' || O.object_name ||
' WHERE upper(' || T.column_name || ') = ''TEXAS''
HAVING count(*) > 0;'
FROM dba_objects O, dba_tab_cols T
WHERE T.table_name = O.object_name and
O.object_type = 'TABLE' and
T.owner = 'HR' and
T.data_type = 'VARCHAR2'
ORDER BY T.table_name,
T.column_name ;
Vous constaterez que j'ai deux fois la même requête, sans arriver à éliminer le doublon. Impossible en effet en SQL de faire un DISTINCT avec un ORDER BY. Soit je trie soit j'élimine les doublons mais pas les deux...
Voilà le résultat de cet ordre SQL.
'SELECTCOUNT(*),'''||T.TABLE_NAME||''','''||T.COLUMN_NAME||''''||'FROM'||O.OBJECT_NAME||'WHEREUPPER('||T.COLUMN_NAME||')=''TEXAS''HAVINGCOUNT(*)>0;'
------------------------------------------------------------------------------------------------------------------------------------------
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS' HAVING count(*) > 0;
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS' HAVING count(*) > 0;
30 rows selected.
SQL> spool off;
On exécute le fichier sql généré.
BINGO : le libellé "TEXAS" est dans la table LOCATIONS, colonne STATE_PROVINCE :-)
Petit problème, le résultat est pollué par les lignes des SELECTs ne rapportant aucun enregistrement.
SQL> @search
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "'SELECTCOU..." - rest of line ignored.
...
no rows selected
no rows selected
no rows selected
...
COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
1 LOCATIONS STATE_PROVINCE
COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
1 LOCATIONS STATE_PROVINCE
...
no rows selected
no rows selected
no rows selected
no rows selected
...
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
Éliminons donc ces lignes parasites "no rows selected" avec le paramètre FEEDBACK de SQL*Plus à OFF.
SQL> set FEED off
SQL> @search
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SP2-0734: unknown command beginning "'SELECTCOU..." - rest of line ignored.
COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
1 LOCATIONS STATE_PROVINCE
COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
1 LOCATIONS STATE_PROVINCE
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)