INFORMIX
Informix Guide to SQL: Reference
Chapter 1: System Catalog
Home Contents Index Master Index New Book

Information Schema

 The Information Schema consists of read-only views that provide information about all the tables, views, and columns on the current database server to which you have access. In addition, Information Schema views provide information about SQL dialects (such as Informix, Oracle, or Sybase) and SQL standards.

This version of the Information Schema views are X/Open CAE standards. Informix provides them so that applications developed on other database systems can obtain Informix system catalog table information without having to use the Informix system catalog tables directly.

Important: Because the X/Open CAE standards Information Schema views differ from ANSI-compliant Information Schema views, Informix recommends that you do not install the X/Open CAE Information Schema views on ANSI-compliant databases.
The following Information Schema views are available:

The following sections contain information about generating and accessing Information Schema views as well as information about their structure.

Generating the Information Schema Views

The Information Schema views are generated automatically when you, as DBA, run the following DB-Access command:

The views are populated by data in the Informix system catalog tables. If tables, views, or stored procedures exist with any of the same names as the Information Schema views, you need to either rename the database objects or rename the views in the script before you can install the views. You can drop the views by using the DROP VIEW statement on each view. Re-create the views by running the script again.

Important: In addition to the columns specified for each Information Schema view, individual vendors might include additional columns or change the order of the columns. Informix recommends that applications not use the forms SELECT * or SELECT table-name* to access an Information Schema view.

Accessing the Information Schema Views

All Information Schema views have the Select privilege granted to PUBLIC WITH GRANT OPTION so that all users can query the views. Because no other privileges are granted on the Information Schema views, they cannot be updated.

You can query the Information Schema views as you would query any other table or view in the database.

Structure of the Information Schema Views

The following views are described in this section:

Most of the columns in the views are defined as VARCHAR data types with large maximums to accept large names and in anticipation of long identifier names in future standards.

TABLES

The tables Information Schema view contains one row for each table to which you have access. It contains the columns that the following table shows.

Column Name Data Type Explanation

table_schema

VARCHAR(128)

Owner of table

table_name

VARCHAR(128)

Name of table or view

table_type

VARCHAR(128)

BASE TABLE for table or VIEW for view

remarks

VARCHAR(255)

Reserved

The visible rows in the tables view depend on your privileges. For example, if you have one or more privileges on a table (such as Insert, Delete, Select, References, Alter, Index, or Update on one or more columns), or if these privileges have been granted to PUBLIC, you see one row describing that table.

COLUMNS

The columns Information Schema view contains one row for each accessible column. It contains the columns that the following table shows.

(1 of 2)

Column Name Data Type Explanation

table_schema

VARCHAR(128)

Owner of table

table_name

VARCHAR(128)

Name of table or view

column_name

VARCHAR(128)

Name of the column of the table or view

ordinal_position

INTEGER

Ordinal position of the column. The ordinal position of a column in a table is a sequential number starting at 1 for the first column.This column is an Informix extension to XPG4.

data_type

VARCHAR(254)

Data type of the column, such as CHARACTER or DECIMAL

char_max_length

INTEGER

Maximum length for character data types; null otherwise

numeric_precision

INTEGER

Total number of digits allowed for exact numeric data types (DECIMAL, INTEGER, MONEY, and SMALLINT), and the number of digits of mantissa precision for approximate data types (FLOAT and SMALLFLOAT), and null for all other data types. The value is machine dependent for FLOAT and SMALLFLOAT.

numeric_prec_radix

INTEGER

Uses one of the following values: 2 approximate data types (FLOAT and SMALLFLOAT)

10 exact numeric data types (DECIMAL, INTEGER, MONEY, and SMALLINT)

Null for all other data types

numeric_scale

INTEGER

Number of significant digits to the right of the decimal point for DECIMAL and MONEY data types: 0 for INTEGER and SMALLINT data types

Null for all other data types

datetime_precision

INTEGER

Number of digits in the fractional part of the seconds for DATE and DATETIME columns; null otherwise. This column is an Informix extension to XPG4.

is_nullable

VARCHAR(3)

Indicates whether a column allows nulls; either YES or NO

remarks

VARCHAR(254)

Reserved

SQL_LANGUAGES

The sql_languages Information Schema view contains a row for each instance of conformance to standards that the current database server supports. The sql_languages Information Schema view contains the columns that the following table shows.

Column Name Data Type Explanation

source

VARCHAR(254)

Organization that defines this SQL version

source_year

VARCHAR(254)

Year the source document was approved

conformance

VARCHAR(254)

Which conformance is supported

integrity

VARCHAR(254)

Indicates whether this is an integrity enhancement feature; either YES or NO

implementation

VARCHAR(254)

Identifies the SQL product of the vendor

binding_style

VARCHAR(254)

Direct, module, or other bind style

programming_lang

VARCHAR(254)

Host language for which the binding style is adopted

The sql_languages Information Schema view is completely visible to all users.

SERVER_INFO

The server_info Information Schema view describes the database server to which the application is currently connected. It contains the columns that the following table shows.

Column Name Data Type Explanation

server_attribute

VARCHAR(254)

An attribute of the database server

attribute_value

VARCHAR(254)

Value of the server_attribute as it applies to the current database server

Each row in this view provides information about one attribute. X/Open-compliant databases must provide applications with certain required information about the database server. The server_info view includes the information that the following table shows.

server_attribute Description

identifier_length

Maximum number of characters for a user-defined name

row_length

Maximum length of a row

userid_length

Maximum number of characters of a user name
(or "authorization identifier")

txn_isolation

Initial transaction isolation level that the database server assumes:

Read Committed
Default isolation level for databases created without logging

Read Uncommitted
Default isolation level for databases created with logging, but not ANSI compliant

Serializable
Default isolation level for ANSI-compliant databases

collation_seq

Assumed ordering of the character set for the database server. The following values are possible:

ISO 8859-1

EBCDIC

The Informix representation shows ISO 8859-1

The server_info Information Schema view is completely visible to all users.



Informix Guide to SQL: Reference, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.