ISO Information Schema

The Information Schema consists of a standardized set of views that contain information about the objects defined in the database. The Information Schema is defined in the ISO/IEC 9075-11 standard and can therefore be expected to be portable and remain stable.

The Information Schema itself is a system schema named information_schema. This schema automatically exists in every database since release v11.49 (Dec2023).

Available information_schema views:

The Information Schema views are defined on top of the Catalog tables and views. They make querying the database catalog easier as you often do not have to join multiple catalog tables yourself. For instance the information_schema.columns view already includes the table_name and table_schema columns so you do not have to join with sys.tables and sys.schemas yourself to get the names of the table and schema. Also sys.comments is already joined in the views where applicable.

Note By default, the information_schema is not in the schema search path (see sys.users.schema_path), so you need to access all views with the information_schema. prefix as shown in the SQL queries below.

SELECT * FROM information_schema.character_sets;

SELECT * FROM information_schema.schemata;
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.views;
SELECT * FROM information_schema.columns;

SELECT schema_name, schema_owner, default_character_set_name, is_system, comments
  FROM information_schema.schemata;

SELECT table_schema, table_name, table_type, comments
  FROM information_schema.tables
 WHERE NOT is_system;

SELECT table_schema, table_name, view_definition, comments
  FROM information_schema.views
 WHERE NOT is_system;

SELECT table_schema, table_name
  FROM information_schema.views
 WHERE table_schema = 'information_schema';

SELECT table_schema, table_name, column_name, ordinal_position, data_type, comments
  FROM information_schema.columns;


SELECT * FROM information_schema.table_constraints;
SELECT * FROM information_schema.referential_constraints;
SELECT * FROM information_schema.check_constraints;

SELECT constraint_schema, table_name, constraint_name, constraint_type
  FROM information_schema.table_constraints
 WHERE NOT is_system;

SELECT constraint_schema, constraint_name, fk_table_name, uc_table_name, update_rule, delete_rule
  FROM information_schema.referential_constraints;

SELECT constraint_schema, table_name, constraint_name, check_clause
  FROM information_schema.check_constraints;


SELECT * FROM information_schema.routines;
SELECT * FROM information_schema.parameters;

SELECT routine_type, routine_schema, routine_name, specific_name, data_type as return_data_type, comments
  FROM information_schema.routines
 WHERE NOT is_system;

SELECT specific_schema, function_name, specific_name, is_result, ordinal_position, parameter_mode, parameter_name, data_type
  FROM information_schema.parameters
 WHERE NOT is_system;


SELECT * FROM information_schema.sequences;

SELECT sequence_schema, sequence_name, current_value, comments
  FROM information_schema.sequences;

Note The Information Schema views do not contain information about all MonetDB objects or specific features; to inquire those you need to query the SQL Catalog tables and views. To support efficient joins between Information Schema views and Catalog tables/views, the Information Schema views have been extended (after the ISO standard columns) with Catalog id columns such as schema_id, table_id, column_id, function_id, etc.