SelectEtoile : Base de connaissance SGBD

Posts Tagged 'primaire'

Catalog MySql

 

Accès au catalogue mysql

 

  • SCHEMATA : informations sur les bases (ou schémas).

  • TABLES : les tables.

  • COLUMNS : leurs colonnes.

  • STATISTICS : les indexes.

  • USER_PRIVILEGES : privilèges globaux.

  • SCHEMA_PRIVILEGES : privilèges sur les bases.

  • TABLE_PRIVILEGES : privilèges sur les tables.

  • COLUMN_PRIVILEGES : privilèges sur les colonnes.

  • CHARACTER_SETS : jeux de caractères.

  • COLLATIONS : interclassements.

  • COLLATION_CHARACTER_SET_APPLICABILITY : correspondances interclassements / charsets.

  • TABLE_CONSTRAINTS : contraintes des tables.

  • KEY_COLUMN_USAGE : clés primaires et étrangères.

  • ROUTINES : les procédures et fonctions stockées.

  • VIEWS : les vues.

  • TRIGGERS : les déclencheurs.

 

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 :

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

 

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 :

  • SELECT * FROM INFORMATION_SCHEMA.ROUTINES

 

Idem pour les triggers :

  • SELECT * FROM INFORMATION_SCHEMA.TRIGGERS

 

 

Tables contenant les infos sur les privileges :

  • USER_PRIVILEGES
  • SCHEMA_PRIVILEGES
  • TABLE_PRIVILEGES
  • COLUMN_PRIVILEGES