Dans un contexte où notre code SQL se
doit d'être exécuté sur différents SGBD, il est important de
connaitre les outils qui sont à notre disposition pour écrire des
requêtes qui vont être portable d'un SGBD à l'autre. Que ce soit
pour valider la présence ou non d'une table, d'une colonne, d'une
contrainte, d'une vue ou d'une stored proc, ou pour en obtenir une
définition, le schéma INFORMATION_SCHEMA est la pour nous aider à
obtenir des informations de base sur les objets de notre base de
données. Pour avoir des informations plus spécifiques, il faudra
toutefois accèder aux tables systèmes des SGBD en question.
Le schéma INFORMATION_SCHEMA est un
schéma ANSI qui est implémenté tant sur SQL Server que sous
SQLite, MySQL, .. Ce schéma vous donne accès à une multitude
d'informations de base sur les objets de la base de données à
laquelle vous êtes connectée. Par contre, bien qu'il existe un
projet OpenSource pour implanter le schéma, Oracle ne le supporte
pas encore de façon native.
Comme c'est une vue ANSI et non
Microsoft, il y a des termes qui diffèrent. On ne parlera pas de
“Database”, mais de “Catalog”, ni de “User-defined type”
mais de “Domain”.
Présentation de
INFORMATION_SCHEMA.TABLES
La vue INFORMATION_SCHEMA.TABLES nous
retourne un enregistrement pour chaque table et chaque vue dans la
base de données qui ne sont pas des objets systèmes. Avec cette
vue, on peut obtenir
Présentation de
INFORMATION_SCHEMA.COLUMNS
La vue INFORMATION_SCHEMA.COLUMNS nous
permet d'obtenir les spécifications des colonnes des tables et des
vues dans notre base de données. En fonction de la valeur de
DATA_TYPE, certaines colonnes seront définies ou non, par exemple,
si le type de données est “int”, la colonne
“CHARACTER_MAXIMUM_LENGTH” ne sera pas définie.
On se servira de cette vue pour se
renseigner sur les colonnes d'une table en particulier, tester
l'existence d'une colonne, ...
Présentation de
INFORMATION_SCHEMA.VIEW
De façon similaire, la vue
INFORMATION_SCHEMA.VIEWS est un sous-ensemble de la vue
INFORMATION_SCHEMA.TABLES ou TABLE_TYPE = 'VIEW', mais ajoute de
nouvelles colonnes d'informations, en l'occurence “VIEW_DEFINITION”
qui nous donnera la définition de la vue si celle-ci a une longueur
de moin de 4000 caractères.
Présentation de
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
La vue
INFORMATION_SCHEMA.VIEW_TABLE_USAGE nous indique les tables
sous-jacentes de la vue. Par exemple, pour la vue
HumanResources.vEmployeeDepartment de la base de données
AdventureWorks2014, on aura ceci :
On peut valider ces informations en
regardant la définition de la vue.
Présentation de
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
La vue
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE va un peu plus loin que la vue
INFORMATION_SCHEMA.VIEW_TABLE_USAGE. En plus de nous lister les
tables sous-jacentes, elle nous donne aussi les colonnes utilisées
dans la vue, pas seulement celles retournées par la vue comme on
peut le voir avec les champs *ID qui sont utilisés dans les
jointures de la requête.
Présentation de
INFORMATION_SCHEMA.ROUTINES
À première vue, la vue
INFORMATION_SCHEMA.ROUTINES peut apporter une certaine confusion sur
les colonnes qu'elle retourne. Plusieurs champs ne seront renseignés
que si la fonctionnalité est supportée sur le SGBD.
Comme par exemple, puisque SQL Server
ne supporte pas les surcharges de procédure, les champs SPECIFIC_*
et ROUTINE_* contiendront les mêmes valeurs. Par contre, sous
PostgreSQL, les champs SPECIFIC_* contiendra des informations
différentes, même d'un serveur à un autre. D'ailleurs, la
documentation de PostgreSQL dit ceci à propos du champ SPECIFIC_NAME
:
The "specific name" of the function. This is a name that uniquely identifies the function in the schema, even if the real name of the function is overloaded. The format of the specific name is not defined, it should only be used to compare it to other instances of specific routine names.
Le “nom spécifique” de la fonction. C'est le nom qui identifie de façon unique la fonction dans le schéma, même si le nom de la function est surchargé. Le format du nom spécifique est indéfini, il doit seulement être utilisé pour le comparer aux noms spécifiques des routines des autres instances.
D'autres champs contiendront une valeur
ou non selon les fonctionnalités du SGBD. Avant d'utiliser cette
vue, il convient de valider auprès du fournisseur des colonnes qui
seront définies.
Il faudrait aussi faire attention, le
champ “ROUTINE_DEFINITION” ne contiendra que les 4000 premier
caractères. Si on veut la définition complèment, il faudra
utiliser la vue système sys.sql_modules.
Présentation de
INFORMATION_SCHEMA.SCHEMATA
Les schémas qui sont présents dans la
base de données se retrouvent dans la vue
INFORMATION_SCHEMA.SCHEMATA. On y retrouvera non seulement les
schémas qu'on a créés, mais aussi les schémas systèmes, comme
par exemple le schéma INFORMATION_SCHEMA ainsi que le schéma sys.
Plusieurs autres tables existent dans
le schéma INFORMATION_SCHEMA. Que ce soit pour connaitre les
contraintes sur les tables ou la sécurité, vous pouvez utilisez ce
schéma pour obtenir les informations de bases sur la base de
données.
Utilisation du schéma
INFORMATION_SCHEMA
On utilise ce schéma pour interroger,
valider ou même générer des scripts lorsqu'on ne peut déterminer
à l'avance si les objets existent et quels sont-ils. Par exemple,
avant d'ajouter ou supprimer une colonne, on peut vouloir valider que
cet objet existe ou non dans la base de données. On pourra écrire
cette validation avant d'effectuer la requête DDL pour éviter des
erreurs lors de l'exécution:
if not exists (select 1 from
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mon_schema' AND
TABLE_NAME = 'ma_table' AND COLUMN_NAME = 'ma_colonne')
BEGIN
alter mon_schema.ma_table add
ma_colonne int;
END
Une autre utilisation du schéma est
qu'il permet de valider les noms d'objets dans la base de données.
Par exemple, si les normes de nomenclature indique qu'il faut une
colonne ayant comme nom le nom de la table suivi de l'identifiant
“id”, une requête permettra de retrouver toute les tables
fautives. Dans la base de données, sur les 71 tables, 36 ne
respectent pas cette nomenclature.
Pour corriger la situation, on peut
aussi se servir du schéma INFORMATION_SCHEMA pour nous générer des
scripts de correction:
Aucun commentaire:
Publier un commentaire