Liste des objets (tables, vues, etc) de tous les schémas de la base
posté en Aug 2021
Pour obtenir une liste des objets (tables, vues, etc) pour tous les schémas présents dans une base PostgreSQL, on peut utiliser la vue correspondante dans les catalogues systèmes pg_catalog et information_schema.
Les vues définies dans information_schema sont définies au format SQL ANSI standard, et se retrouvent dans plusieurs moteurs de base de données (Sql server, MySql, PostgreSQL, etc). Les requêtes effectuée au travers du catalogue information_schema seront plus portable entre les différents moteurs de base de données. Par contre, si vous souhaitez afficher des informations spécifiques au moteur PosgreSQL, alors il faudra utiliser les vues de pg_catalog.
Pour obtenir une liste des objets (tables, vues, etc) pour tous les schémas présents dans une base PostgreSQL, on peut utiliser la vue correspondante dans le catalogue système pg_catalog de la base de données.
Par exemple, la requête suivante liste les tables présentes sur les schémas production et recette en utilisant la vue pg_catalog.pg_tables :
sql> SELECT * FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
production | article | system | | t | f | f | f
production | stock | system | | t | f | f | f
recette | article | system | | t | f | f | f
(3 rows)
Dans le catalogue pg_catalog, il y a plus d'une soixantaine de vues permettant de lister la composition de la base, tel que, par exemple, pg_indexes pour la liste des index, pg_user pour lister les utilisateurs, etc.
La clause WHERE de la requête précédente exclue les schémas systèmes pg_catalog et information_schema pour ne lister que le contenu des schémas utilisateurs.
Pour connaitre l'ensemble des vues disponibles de pg_catalog, on utilise la vue pg_catalog.pg_views :
sql> select viewname from pg_catalog.pg_views;
viewname
---------------------------------------
pg_shadow
pg_roles
pg_settings
pg_file_settings
pg_hba_file_rules
pg_config
...
(128 rows)
Pour lister la composition d'un table, liste des colonne et le type des données, on utilise le catalogue information_schema avec sa vue columns.
Ici, par exemple, on liste la composition de la table stock avec le nom des colonnes et leurs type :
sql> SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'stock';
table_name | column_name | data_type
------------+----------------+-----------
stock | stock_mvt_id | bigint
stock | article_id | bigint
stock | stock_mvt_qte | integer
stock | stock_mvt_date | date
stock | stock_mvt_oper | character
(5 rows)
Pour lister l'ensemble des schémas, tables, colonnes et contraintes associées, on peut utiliser la requête suivante :
sql> SELECT
INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME NOT LIKE ('pg_%')
GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_CATALOG,
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
ORDER BY 1, 2, 3, 5 desc;
table_catalog | table_schema | table_name | column_name | constraint_type
---------------+--------------+------------+----------------+-----------------
tutorial | production | article | article_id | PRIMARY KEY
tutorial | production | article | article_name | PRIMARY KEY
tutorial | production | article | article_option | PRIMARY KEY
tutorial | production | article | article_price | PRIMARY KEY
tutorial | production | article | article_id | CHECK
tutorial | production | article | article_name | CHECK
tutorial | production | article | article_option | CHECK
tutorial | production | article | article_price | CHECK
tutorial | production | stock | article_id | PRIMARY KEY
tutorial | production | stock | stock_mvt_date | PRIMARY KEY
tutorial | production | stock | stock_mvt_id | PRIMARY KEY
tutorial | production | stock | stock_mvt_oper | PRIMARY KEY
tutorial | production | stock | stock_mvt_qte | PRIMARY KEY
tutorial | production | stock | article_id | CHECK
tutorial | production | stock | stock_mvt_date | CHECK
tutorial | production | stock | stock_mvt_id | CHECK
tutorial | production | stock | stock_mvt_oper | CHECK
tutorial | production | stock | stock_mvt_qte | CHECK
tutorial | recette | article | article_id | PRIMARY KEY
tutorial | recette | article | article_name | PRIMARY KEY
tutorial | recette | article | article_option | PRIMARY KEY
tutorial | recette | article | article_price | PRIMARY KEY
tutorial | recette | article | article_id | CHECK
tutorial | recette | article | article_name | CHECK
tutorial | recette | article | article_option | CHECK
tutorial | recette | article | article_price | CHECK
(26 rows)