Navigation:  Advantage Developer's Guide > Part II - Advantage SQL > Chapter 14 - System Management and Metadata >

     Getting Table-Related Information

Advantage Database Server v8.1: A Developer’s Guide

by Cary Jensen and Loy Anderson

  © 2007 Cary Jensen and Loy Anderson. All rights reserved.

Previous pageReturn to chapter overviewNext page

There are five system tables that provide you with information about your dictionary-bound tables: system.tables, system.columns, system.indexes, system.indexfiles, and system.triggers. The table system.tables contains one record for each table in your data dictionary. (Views are not listed in this table. You can get view information from the system.views table.) Columns in this table contain information about a table's name, physical location on disk, encryption, permissions level, primary key, and record-level constraint, to name a few.

The following query retrieves information about all nonsystem tables in the data dictionary:

SELECT * FROM system.tables

The next query returns one record with information about the PRODUCTS table:

SELECT * FROM system.tables
 WHERE NAME = 'PRODUCTS'

The system.columns table contains one record for every field in every table in your data dictionary. Fields in this table include the name of the field, the name of the table to which the field belongs, the ordinal position of the field in its table structure, column type, constraints, and so forth.

The values in the Field_type column are integer codes. Table 14-1 contains the values for these codes.

 

Type

Value

ADS_UNKNOWN

0

ADS_LOGICAL

1

ADS_NUMERIC

2

ADS_DATE

3

ADS_STRING

4

ADS_MEMO

5

ADS_BINARY

6

ADS_IMAGE

7

ADS_VARCHAR

8

ADS_COMPACTDATE

9

ADS_DOUBLE

10

ADS_INTEGER

11

ADS_SHORTINT

12

ADS_TIME

13

ADS_TIMESTAMP

14

ADS_AUTOINC

15

ADS_RAW

16

ADS_CURDOUBLE

17

ADS_MONEY

18

ADS_LONGLONG

19

ADS_CISTRING

20

ADS_ROWVERSION

21

ADS_MODTIME

22

ADS_NUMBER

23

Table 14-1: Field Type Codes