INFORMATION_SCHEMA Views - Mimer Developer

Introduction

All relational databases store information about themselves in a collection of system tables. These internal system tables contain meta-data, i.e. data about objects in the database. Mimer has always discouraged users from writing queries directly against the system tables because the table structure is subject to change. Previously, to get information on the contents of the database, we had predefined Mimer specific views.

As Mimer’s policy is to develop Mimer SQL as far as possible in accordance with the established standards, Mimer SQL uses information schema views that conform to the ISO SQL definition for the INFORMATION_SCHEMA.

Description

The views in the Information Schema are viewed tables, defined in terms of the internal system base tables. The views are defined as being in a schema named INFORMATION_SCHEMA, enabling these views to be accessed in the same way as any other tables in any other schema.

SELECT on these views is granted to PUBLIC WITH GRANT OPTION, so that they can be queried by any user and so that SELECT privilege can be further granted on views that reference the Information Schema views. No other privilege is granted on them, so they cannot be updated.

Note that an SQL-implementation may define objects associated with the Information Schema that are not defined in the standard. An SQL-implementation or any future version of the standard may also add columns to the views that are defined. Mimer SQL has some objects (e.g. databanks) that are not standard. For these objects, we have Information Schema views with table names beginning with “EXT_”.

The views that describe objects (TABLES, DOMAINS, ROUTINES etc.) contain columns that specify the catalog in which the object resides. As Mimer SQL does not support catalog names, these columns contain a zero-length string.

Function

The Information Schema views are read-only views from which users can retrieve information about any database objects they own or to which they have some access. The description for each view indicates exactly what information is shown to the user in the view.

An INFORMATION_SCHEMA view can be read with an ordinary select statement.

Examples:

To get a list of all tables excluding views in the database to which you have access, you can use the following statement:

SELECT TABLE_SCHEMA, TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'

To get the names and types for constraints defined for the table EXAMPLES.MANAGERS, you can use the following statement:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE TABLE_SCHEMA = 'EXAMPLES'
   AND TABLE_NAME = 'MANAGERS'

To get the textual definition for the stored procedure ALLOCATE_ROOM in schema EXAMPLES, you can use the following statement:

SELECT ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_SCHEMA = 'EXAMPLES'
   AND ROUTINE_NAME = 'ALLOCATE_ROOM'

Note that the standard view ROUTINES returns the NULL value if the definition does not fit into the maximum length, which is 200 characters. To get the whole definition, Mimer SQL supports a view that gives you the definition in rows of 400 characters:

SELECT SOURCE_DEFINITION
  FROM INFORMATION_SCHEMA.EXT_SOURCE_DEFINITION
 WHERE OBJECT_SCHEMA = 'EXAMPLES'
   AND OBJECT_NAME = 'ALLOCATE_ROOM'
   AND OBJECT_TYPE = 'PROCEDURE'

Benefits

All applications that need to obtain information about any database object should use the Information Schema views. Using these views makes it possible to move applications between different database systems (supporting the ISO definition) without the need to rewrite a substantial amount of code.

Literature References

ISO/IEC 9075:1992(E) Information technology – Database languages – SQL.
ISO/IEC 9075:1999(E) Information technology – Database languages – SQL.

Links

For a detailed description see the Data Dictionary Views in the Reference Manual in the Mimer SQL Documentation set.

Graphic Element - Cube