The PUBLIC schema contains views of selected system tables. In general, the views are restricted in such a way that a user without DBA privileges may obtain information regarding only those objects owned by the user or upon which the user has some privilege. The user DBA, as the database “super-user”, may query the public views and retrieve information about all objects in the database.
The PUBLIC schema contains the following views:
- AUTHORIZATIONS
- SCHEMAS
- TABLES
- COLUMNS
- DOMAINS
- CONSTRAINTS
- CONSTRAINTCOLS
- FOREIGNKEYS
- TABPRIVS
- COLPRIVS
- SCHEMAPRIVS
- DOMAINPRIVS
- DATATYPES
- VIEWTEXT
These views may be queried in the way that tables are normally queried using SELECT statements. For example, the following query retrieves all available information regarding domains owned by the user executing the query or upon which the user has some privilege:
SELECT * FROM PUBLIC.domains;
The AUTHORIZATIONS view contains authorization information about the user executing the query, as well as information about all other users defined for the database.
The following table describes each of the columns in the AUTHORIZATIONS view:
COLUMN DESCRIPTION name user name authID system-generated surrogate ID for the user defaultDB dbid of the default database for the user defaultSchema schemaID of the default schema for the user; if null, the user begins in the PUBLIC schema when connecting creator authID of the creator of the authorization created timestamp when the authorization was created passwordChange timestamp when the password was last changed; if null, password has never been changed
The SCHEMAS view has one row for each schema in the database.
The following table describes each of the columns in the SCHEMAS view:
COLUMN DESCRIPTION name schema name dbid dbid of the database that owns the schema schemaID system-generated surrogate ID for the schema authID authID of the owner of the schema created timestamp when the schema was created
The TABLES view has one row for each table or view owned by the user, or upon which the user has some privilege. If the user has some privilege on a schema, information about all tables/views in the schema is also returned by the query. If the user possesses DBA privileges, information about all tables/views in the database is returned by the query. The following table describes each of the columns in the TABLES view:
COLUMN DESCRIPTION name table name tableID system-generated surrogate ID for the table schemaID schemaID of the schema to which the table belongs type B: base table
L: linked table
N: snapshot table
R: view or partitioned table
S: system tableremarks textual remarks associated with the table creator authID of the creator of the table created timestamp when the table was created altered timestamp when the table was last altered; if null, the table has never been altered
The COLUMNS view has one row for each column (belonging to either a table or a view) in the database that is owned by the user, or upon which the user has some privilege. If the user has some privilege on a schema, information about all columns belonging to tables/views in the schema is also returned by the query. If the user possesses DBA privileges, information about all columns in the database is returned by the query.
The following table describes each of the columns in the COLUMNS view:
COLUMN DESCRIPTION name column name tableID tableID of the table to which the column belongs colno ordinal position of the column, in terms of order of column definition storeno physical position of the column in storage datatype encoded representation of the base datatype of the column (refer to the DATATYPES view below) domainID domainID of the domain with which the column is associated length maximum length in bytes permitted for values in the column (note that this is unrelated to data storage size) scale for numeric data types, number of digits after the decimal point creator authID of the creator of the table created timestamp when the column was created altered timestamp when the column was last altered; if null, column has never been altered remarks textual remarks associated with the column
The DOMAINS view has one row for each domain owned by the user, or upon which the user has some privilege. If the user has some privilege on a schema, information about all domains belonging to the schema is also returned by the query. If the user possesses DBA privileges, information about all domains in the database is returned by the query.
The following table describes each of the columns in the DOMAINS view:
COLUMN DESCRIPTION name domain name (system domains begin with the underscore character: "_") datatype encoded representation of the base type of the data elements associated with the domain (refer to the DATATYPES view below) length maximum length in bytes permitted for values in the domain (note that this is unrelated to data storage size) scale for numeric types, the maximum number of digits permitted for instantiations of values in the domain domainID system-generated surrogate ID for the domain schemaID schemaID of the schema to which the domain belongs default handle associated with the expression representing the default value for the domain; if null, there is no default value DefaultSize size (in bytes) of the execution-ready form of the default value (if present) checkName name of the check constraint for the domain; if null, there is no check constraint check handle of the execution-ready form of the check constraint (if present) checkSize size (in bytes) of the execution-ready form of the check constraint (if present) creator authID of the creator of the domain created timestamp when the domain was created collation collation type for the character domain
2260: BINARY
2261: BINARY_UPCASE
2262: LATIN01
2263: LATIN01_UPCASE
2268: LATIN02
2269: LATIN02_UPCASE
(null if the domain is not a character type, or if the collation type is unknown)
The CONSTRAINTS view has one row for each constraint on any tables owned by the user, or upon which the user has some privilege. If the user has some privilege on a schema, information about all constraints belonging to tables in the schema is also returned by the query. If the user possesses DBA privileges, information about all constraints in the database is returned by the query.
The following table describes each of the columns in the CONSTRAINTS view:
COLUMN DESCRIPTION name constraint name; if the user does not specify a constraint name, the system will automatically generate one type PK: primary key
FK: foreign key
UK: unique key
NN: not nullconstraintID system-generated ID for the constraint schemaID schemaID of the schema to which the table owning the constraint belongs tableID tableID of the table with which the constraint is associated enabled Y: the constraint is being enforced
N: the constraint is not being enforcedenforceID QEPid of the QEP that enforces the constraint creator authID of the creator of the constraint created timestamp when the constraint was created
The CONSTRAINTCOLS view has one row for each column that is declared NOT NULL, or participates in a PRIMARY KEY, FOREIGN KEY, or UNIQUE KEY constraint, in all tables owned by the user or upon which the user has some privilege. If the user has some privilege on a schema, information about all columns participating in a constraint in the schema is also returned by the query. If the user possesses DBA privileges, information about all columns participating in a constraint in the database is returned by the query.
The following table describes each of the columns in the CONSTRAINTCOLS view:
COLUMN DESCRIPTION constraintID constraintID of the constraint definition tableID tableID of the table with which the constraint is associated colno colno of the column participating in the constraint keyseq ordinal position of the column in the constraint definition; null if the constraint is a check constraint
The FOREIGNKEYS view has one row for each FOREIGN KEY constraint in all tables owned by the user or upon which the user has some privilege.
The following table describes each of the columns in the FOREIGNKEYS view:
COLUMN DESCRIPTION constraintID constraintID of the FOREIGN KEY constraint definition refConstraintID constraintID of the referenced PRIMARY KEY or UNIQUE constraint onUpdate The rule for operations involving updates on referenced columns:
C: CASCADE
D: SET DEFAULT
N: SET NULL
R: RESTRICTonDelete The rule for operations involving deletes on referenced columns:
C: CASCADE
D: SET DEFAULT
N: SET NULL
R: RESTRICTupdateQEPID QEPid of the QEP enforcing the update rule on the referencing constraint (-1 if there is no QEP) deleteQEPID QEPid of the QEP enforcing the delete rule on the referencing constraint (-1 if there is no QEP) orphanQEPID QEPid of the QEP enforcing the parent-child relationship of the foreign key when inserts are executed on the child table (-1 if there is no QEP)
The TABPRIVS view has one row for each table or view on which the user has been granted some privilege. If the user possesses DBA privileges, information about all table/view privileges in the database is returned by the query.
The following table describes each of the columns in the TABPRIVS view:
COLUMN DESCRIPTION grantor authID of the user who granted the privilege grantee authID of the user who received the privilege TableID tableID of the table to which the privileges apply privilege S: select
D: delete
U: update
I: insert
R: referencesgrantable Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the granteegranted timestamp when the privilege was granted
The COLPRIVS view has one row for each table column or view column on which the user has been granted some privilege. If the user possesses DBA privileges, information about all column privileges in the database is returned by the query.
The following table describes each of the columns in the COLPRIVS view:
COLUMN DESCRIPTION grantor authID of the user who granted the privilege grantee authID of the user who received the privilege tableID tableID of the table containing the column to which the privileges apply colno colno of the column to which the privileges apply Privilege S: select
I: insert
U: update
R: referencesgrantable Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the granteegranted timestamp when the privilege was granted
The SCHEMAPRIVS view has one row for each schema on which the user has been granted some privilege. If the user possesses DBA privileges, information about all schema privileges in the database is returned by the query.
The following table describes each of the columns in the SCHEMAPRIVS view:
COLUMN DESCRIPTION grantor authID of the user who granted the privilege grantee authID of the user who received the privilege schemaID schemaID of the schema to which the privileges apply privilege S: select
D: delete
U: update
I: insert
G: usage
R: references
A: ownergrantable Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the granteegranted timestamp when the privilege was granted
The DOMAINPRIVS view has one row for each domain on which the user has been granted some privilege. If the user possesses DBA privileges, information about all domain privileges in the database is returned by the query.
The following table describes each of the columns in the DOMAINPRIVS view:
COLUMN DESCRIPTION grantor authID of the user who granted the privilege grantee authID of the user who received the privilege domainID domainID of the domain to which the privileges apply privilege G: usage grantable Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the granteegranted timestamp when the privilege was granted
The DATATYPES view has one row for each SAND CDBMS data type.
The following table describes each of the columns in the DATATYPES view:
COLUMN DESCRIPTION datatype encoded representation of the data type type name for the data type
Selecting from the DATATYPES view will return the following results table:DATATYPE TYPE
----------- ---------
1 SMALLINT
2 INTEGER
3 FLOAT
4 DATE
6 CHAR
7 TIME
8 DECIMAL
9 VARCHAR
11 TIMESTAMP
15 REAL
21 UNSIGNED
23 BLOBThis table can be referred to when querying the COLUMNS or DOMAINS views, which return the encoded representation of a data type instead of the data type name.
Note that any other data type listed among the results can be ignored, as it is either unused or used only internally.
The VIEWTEXT view provides information about each view owned by the user, or upon which the user has some privilege. If the user has some privilege on a schema, information about all views in the schema is also returned by the query. If the user possesses DBA privileges, information about all views in the database is returned by the query. The query results table displays one row for every 80 characters in the SQL text definition of a view.
The following table describes each of the columns in the VIEWTEXT view:
COLUMN DESCRIPTION viewID system-generated ID for the view definition lineno line number of the view definition text text view definition in textual form