helpinghand
search
needassistance
 
Features
INFORMATION_SCHEMA Views
Category: SQL
Introduction

All relational databases store information about themselves in a collection of non-standard 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 8.2, and later versions, uses the new information schema views that conform to the ISO SQL92 definition for the INFORMATION_SCHEMA. In addition, we also have information schema views for procedures and triggers that conform to the ISO SQL99 definition. In other words, Mimer has exposed an ISO-standard map of the internal system tables.

Description

The views of 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 and also objects (e.g. sequences) for which no standard yet exists. 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 (see Mimer SQL Reference Manual) for each view indicates exactly what information is shown to the user in the view.

An INFORMATION_SCHEMA view can be read with the statement (note the qualified form of view-name):

SELECT column-list
FROM INFORMATION_SCHEMA.view-name
WHERE condition


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.TABLES
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 2000 characters. To get the whole definition, Mimer SQL supports a view that gives you the definition in rows of 5000 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.

Links

Mimer SQL Documentation Set, PDF-file. (See the Reference Manual.)

Mimer SQL Documentation Set, html navigation. (See the Reference Manual.)

Literature References

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


Last updated: 2010-02-03

 

Powered by Mimer SQL

Powered by Mimer SQL