Catalog MySql

1  

Accès au catalogue mysql

 

 

Exemples de requete sur le CATALOG :

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'nom_base';

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'nom_base';

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'nom_base' AND TABLE_NAME = 'nom_table' ORDER BY ORDINAL_POSITION;

 

Visualisation de la clé primaire :

 

Visualisation des colonnes indexes :

SELECT COLUMN_NAME, COLUMN_KEY

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table'

AND COLUMN_KEY IS NOT NULL

ORDER BY ORDINAL_POSITION;

 

 

Visualisation des infos sur les indexes :

SELECT *

FROM INFORMATION_SCHEMA.STATISTICS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table';

 

 

Infos sur les contraintes d’intégrité :

SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_SCHEMA = 'nom_base'

AND TABLE_NAME = 'nom_table'

AND CONSTRAINT_TYPE = 'FOREIGN KEY';

 

SELECT k.CONSTRAINT_SCHEMA, k.CONSTRAINT_NAME, k.TABLE_NAME, k.COLUMN_NAME

, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_TABLE_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c

ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME

WHERE c.CONSTRAINT_TYPE = 'FOREIGN KEY';

 

Liste des clés étrangères non indexes :

SELECT k.CONSTRAINT_SCHEMA, k.CONSTRAINT_NAME, k.TABLE_NAME, k.COLUMN_NAME

, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c

ON k.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA

AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME

LEFT JOIN INFORMATION_SCHEMA.STATISTICS AS s

ON k.CONSTRAINT_SCHEMA = s.TABLE_SCHEMA

AND k.TABLE_NAME = s.TABLE_NAME

AND k.COLUMN_NAME = s.COLUMN_NAME

WHERE c.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND s.INDEX_NAME is null;

 

Table contenant les infos sur les ps et fonctions :

 

Idem pour les triggers :

 

 

Tables contenant les infos sur les privileges :