INFO statement

Use the INFO statement to list the names of all the user-defined tables in the current database, or to display information about a specific table.

Syntax

This statement is an extension to the ANSI/ISO standard for SQL. You can use this statement only with DB-Access.

INFO { TABLES | { { COLUMNS | INDEXES | STATUS } | { PRIVILEGES | ACCESS } | { FRAGMENTS | REFERENCES } } FORtable }

Element Description Restrictions Syntax
table Table about which you seek information Must exist Database Object Name

Usage

The INFO TABLES statement lists the names of all the user-defined tables in the current database. Other keywords that can immediately follow the INFO keyword instruct DB-Access to display various attributes of the table whose name follows the FOR keyword. To display information from more than one keyword option, issue multiple INFO statements.

The keyword options that the INFO statement supports can display the following information:

  • TABLES Keyword

    Use TABLES (with no FOR clause) to list the identifier of every table in the current database, not including system catalog tables. Each user-defined table is listed in one of the following formats:

    • If you are the owner of the cust_calls table, it appears as cust_calls.
    • If you are not the owner of the cust_calls table, the authorization identifier of the owner precedes the table name, such as 'june'.cust_calls.
  • COLUMNS Keyword

    Use COLUMNS to display the names and data types of the columns in the specified table, showing for each column whether NULL values are allowed.

  • INDEXES Keyword

    Use INDEXES to display the name, owner, and type of each index of the specified table, the clustered status, and listing the indexed columns.

  • FRAGMENTS Keyword

    Use FRAGMENTS to display the fragmentation strategy and the names of the dbspaces storing the fragments of a fragmented table. If the table is fragmented with an expression-based distribution scheme, the INFO statement also shows the expressions.

  • ACCESS or PRIVILEGES Keyword

    Use ACCESS or PRIVILEGES to display the discretionary access privileges currently held by users, roles, and the PUBLIC group for the specified table. (These two keywords are synonyms in this context.)

  • REFERENCES Keyword

    Use REFERENCES to display the References access privilege for users who can define referential constraints on the columns of the specified table. For database-level privileges, use a SELECT statement to query the sysusers system catalog table.

  • STATUS Keyword

    Use STATUS to display information about the owner, row length, number of rows and columns, creation date, and the status of audit trails for the specified table.

An alternative to using the INFO statement of SQL is to use the Info command of the SQL menu or of the Table menu of DB-Access to display the same and additional information.

Examples

Use the following example to list the user tables in a database: 
INFO TABLES;
To display information about a specific table, use the syntax: 
INFO info_keyword FOR table
Here table is the table name and info_keyword is one of the seven keyword options, besides TABLES, to the INFO statement. For example, to display the names of the columns of the table customer, use this statement: 
INFO COLUMNS FOR customer;
This example produces the following output: 
Column name          Type              Nulls

customer_num         serial            no
fname                char(15)          yes
lname                char(15)          yes
company              char(20)          yes
address1             char(20)          yes
address2             char(20)          yes
city                 char(15)          yes
state                char(2)           yes
zipcode              char(5)           yes
phone                char(18)          yes