System Tables
|
Both ADS and ALS provide 16 system tables that you can query to retrieve metadata about your data dictionaries and the objects they contain. These tables are available when you are connected to ADS or ALS using a data dictionary.
You use system tables to discover the properties of the data dictionary you are connected to, the names of tables bound to that data dictionary, individual table structures, index file names, index order names, constraints, views, and stored procedures. If you are connected to a data dictionary using the administrative account (ADSSYS), you can also get information about referential integrity constraints, users, groups, and access permissions.
In order to ensure that system table names are unique and identifiable, all system tables are qualified using dot notation with the “system” schema. For example, you use the system.columns table to obtain information about the individual columns available in your database tables, and the system.users table to retrieve information about the data dictionary’s users.
You access system tables by querying them. For example, the following query returns information about the stored procedures in the data dictionary to which you are connected:
SELECT * FROM system.storedprocedures
System tables are often used to automate the management of your data dictionary from your client applications. For example, if your client application creates a new table that must be available to all users, you can use information in the system.users and system.usergroupmembers system tables to determine which groups and users need to have access rights granted to them.
The actual granting of the rights is something that your client application can do using the SQL GRANT statement (described later in this chapter). Note, however, that creating automated operations like these requires that the client application connect to the data dictionary using the administrative user name, ADSSYS.
| Note | Client applications should not use the administrative connection for normal data processing. However, when a client application needs to perform some administrative function, such as granting access rights, it is perfectly acceptable to establish a second, temporary connection using the data dictionary administrative account, which you close once the administrative operation is complete. |
In order to protect confidential information, the information that is returned in a system table query is sensitive to the rights of the user whose connection is used for the query. When queried using the data dictionary administrative connection, all information contained in the data dictionary is available. By comparison, user connections are limited as to what information can be retrieved. The system table may not be accessible, or the system table retrieved contains less information.
The following sections are divided into related topics, where one or more system tables are discussed in each section. Each discussion includes the primary purpose of the table or tables. In many cases, individual fields in the returned result sets are introduced, and some example queries are given. However, no attempt is made to list the structures of the tables begin examined. That information is available in the ADS help. Also, additional fields will likely be added to these tables in future versions of ADS. You can easily discover the column names of each of these tables by executing a simple SELECT * FROM query.
Getting Dictionary Information
You retrieve the properties of your data dictionary using the system.dictionary table. This table has one record, which contains one field for each property of the data dictionary to which you are connected. The following is how a query of this system table looks:
SELECT * FROM system.dictionary
If you are connected to the data dictionary using the administrator’s account, every column in this record is populated with the associated property value. If you are connected on any other account, only the Version_major and Version_minor columns of this table are populated all other columns contain NULL values.
Getting Table-Related Information
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 UCASE(Name) = 'PRODUCTS'
| Note | The Name field in the preceding query, which holds the names of the tables in the data dictionary, is converted to uppercase using the UCASE SQL scalar function. String values are case sensitive, and this conversion ensures that you will retrieve the record for the PRODUCTS table, regardless of the case of this name in the system.tables result set. |
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 11-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 |
The following query demonstrates how to retrieve the column names for the EMPLOYEE table:
SELECT Name FROM system.columns WHERE UCASE(Parent) = 'EMPLOYEE'
The system.indexes table returns one record for each index order in the data dictionary. Columns in this table include the name of the index order, the table it is associated with, the index file it is associated with, and the type of index, as well as FTS (full text search) options such as noise words, delimiters, and drop characters. The index options field contains an integer, which consists of the sum of the index options shown in Table 11-2. For example, a unique, compound ascending index, with no other options, has the index option value of 3, which is the sum of ADS_ ASCENDING, ADS_UNIQUE, and ADS_COMPOUND.
| Type | Value |
|---|---|
| ADS_ASCENDING | 0 |
| ADS_UNIQUE | 1 |
| ADS_COMPOUND | 2 |
| ADS_CUSTOM | 4 |
| ADS_DESCENDING | 8 |
| ADS_USER_DEFINED | 16 |
| ADS_FTS_INDEX | 32 |
| ADS_FTS_FIXED | 64 |
| ADS_CASE_SENSITIVE | 128 |
| ADS_FTS_KEEP_SCORE | 256 |
| ADS_FTS_PROTECT_NUMBERS | 512 |
The following query returns a list of all index orders in the structural index of the ITEMS table:
SELECT * FROM system.indexes WHERE UCASE(Parent) = 'ITEMS' AND UCASE(Index_File_Name) = 'ITEMS.ADI'
You use the system.indexfiles table to return one record for each index file in the data dictionary. The columns of this table include the index filename, the table it is associated with, the path to the file, and the index file page size.
The system.triggers table returns one record for each registered trigger in the data dictionary. Columns returned by this table include the trigger name, the table it is associated with, trigger type, trigger event type, trigger container type, the trigger’s SQL (where appropriate), the function name (for trigger containers), trigger priority, and trigger options. The values for the trigger type, trigger event type, container type, and trigger options are integer codes. These codes are listed in Table 11-3, Table 11-4, Table 11-5, and Table 11-6, respectively.
| Type | Value |
|---|---|
| ADS_TRIGEVENT_INSERT | 1 |
| ADS_TRIGEVENT_UPDATE | 2 |
| ADS_TRIGEVENT_DELETE | 3 |
| Type | Value |
|---|---|
| ADS_TRIGTYPE_BEFORE | 1 |
| ADS_TRIGTYPE_INSTEADOF | 2 |
| ADS_TRIGTYPE_AFTER | 4 |
| Type | Value |
|---|---|
| ADS_TRIG_WIN32DLL | 1 |
| ADS_TRIG_COM | 2 |
| ADS_TRIG_SCRIPT | 3 |
| Type | Value |
|---|---|
| ADS_TRIGOPTIONS_NO_VALUES | 0 |
| ADS_TRIGOPTIONS_WANT_VALUES | 1 |
| ADS_TRIGOPTIONS_WANT_MEMOS_AND_BLOBS | 2 |
| ADS_TRIGOPTIONS_DEFAULT | 3 |
| ADS_TRIGOPTIONS_NO_TRANSACTION | 4 |
Getting User and Group Information
There are four tables that contain information about users, groups, and permissions: system.users, system.usergroups, system.usergroupmembers, and system.permissions. With the exception of the system.permissions table, you must be connected to the data dictionary using the ADSSYS administrative account to access this information.
You use the system.users table to retrieve one record for each user defined in the data dictionary. The columns in this table provide you with the user’s name, description, and whether or not the user is enabled for Internet access. (ADSSYS is not included in this table.) The following query returns the names of the users defined for the connected dictionary:
SELECT Name FROM system.users
The system.usergroups table contains one record for each group defined for your data dictionary. Columns returned in this result set include the group name and description. The following query demonstrates how to retrieve the names of your data dictionary’s groups:
SELECT Name FROM system.usergroups
To discover which groups your data dictionary’s users are assigned to, you query the system.usergroupmembers table. This table has one record for each group to which each user belongs. (Recall that a given user may be a member of zero or more groups.) There are only two fields in this group: Name and Group_Name.
You use the system.permissions table to discover the names of objects, object type, name of user or group with access rights, object parent (when applicable), rights conveyed, and whether or not the rights are inherited. The values for the object type field in this table are codes. These codes are shown in Table 11-7.
| Type | Value |
|---|---|
| ADS_DD_UNKNOWN_OBJECT | 0 |
| ADS_DD_TABLE_OBJECT | 1 |
| ADS_DD_RELATION_OBJECT | 2 |
| ADS_DD_INDEX_FILE_OBJECT | 3 |
| ADS_DD_FIELD_OBJECT | 4 |
| ADS_DD_COLUMN_OBJECT | 4 |
| ADS_DD_INDEX_OBJECT | 5 |
| ADS_DD_VIEW_OBJECT | 6 |
| ADS_DD_VIEW_OR_TABLE_OBJECT | 7 |
| ADS_DD_USER_OBJECT | 8 |
| ADS_DD_USER_GROUP_OBJECT | 9 |
| ADS_DD_PROCEDURE_OBJECT | 10 |
| ADS_DD_DATABASE_OBJECT | 11 |
| ADS_DD_LINK_OBJECT | 12 |
| ADS_DD_TABLE_VIEW_OR_LINK_OBJECT | 13 |
| ADS_DD_TRIGGER_OBJECT | 14 |
All information is available from an administrative account connection. The system.permissions table access from an administrative connection will include the access rights assigned for each specific user and group to each object in the data dictionary.
From a user connection, all objects in the data dictionary are shown in this table, but only that user’s access rights information is displayed. The user’s access rights displayed are the cumulative rights assigned to the user and those rights inherited from any groups to which that user also belongs.
Getting Other Object Information
There are four additional system tables that you use to retrieve information about data dictionary objects. The system.relations table can only be queried from the administrative account. The system.links, system.storedprocedures, and system.views tables can be accessed using either the administrative or a user account, although some column values in the result set may be NULL when queried from the user account.
You use system.relations to return one record for each referential integrity relationship defined in your data dictionary. Columns returned in this result set include RI name, parent table, child table, parent table primary index order name, child table foreign key index order name, update rule, delete rule, and related error messages. The update and delete rule values are codes. The values for these codes are listed in Table 11-8.
| Type | Value |
|---|---|
| ADS_DD_RI_CASCADE | 1 |
| ADS_DD_RI_RESTRICT | 2 |
| ADS_DD_RI_SETNULL | 3 |
| ADS_DD_RI_SETDEFAULT | 4 |
You use the system.links table to retrieve one record for each available data dictionary link. Data returned in this result set includes the link name, path to the linked dictionary, link options, and user name associated with the link. Link options are represented by an integer value. The value that appears in the system.links table is the sum of the link options listed in Table 11-9.
| Type | Value |
|---|---|
| ADS_LINK_GLOBAL | 1 |
| ADS_LINK_AUTH_ACTIVE_USER | 2 |
| ADS_LINK_PATH_IS_STATIC | 4 |
The system.storedprocedures table returns one record for each visible, registered stored procedure. Columns returned in this result set include the name of the stored procedure object, the AEP container name, input parameters, output parameters, and the name of the function in the AEP container.
You query the system.views table to retrieve one record for each visible view. Fields returned by this query include view name, length of the SQL statement that defines the view, and the view’s SQL statement.
Other System Tables
There are two final system tables that do not fall into any of the preceding categories. These tables, system.objects and system.iota, can be accessed using either the administrative or a user connection.
The system.objects table returns information about the structure of each system table, with the exception of system.objects and system.iota. Each record includes a system table name (minus the system schema qualification), the parent table (where appropriate), the fields on which the table is sorted, and the primary key of the table.
The system.iota table is a trivial table that contains one field, named iota, and one record with the NULL value in the iota field. (Iota, the ninth letter in the Greek alphabet, is also a term that means “infinitesimally small.”) This table permits you to execute queries that select from SQL scalar functions and other expressions with a minimum of overhead. For example, consider the following query:
SELECT USER() FROM system.iota
This query returns the name of the user over whose connection the query is executed. Here are some more examples, along with comments, to give some ideas about how you can use this system table:
//Returns a random integer SELECT RAND() FROM system.iota; //Returns the qualified data dictionary file name SELECT DATABASE() FROM system.iota; //Returns an integer reflecting the current day of the week SELECT DAYOFWEEK(CURDATE()) FROM system.iota
|
EAN: 2147483647
Pages: 129