Catalog Details

The Management Portal provides Catalog Details information for each Table, View, Procedure, and Cached Query. The filtering schema contents (left side) component of the Management Portal SQL interface allows you to select an individual item to display its Catalog Details.

Catalog Details for a Table

The following Catalog Details options are provided for each table:

Table Info: Table Type: either TABLE, GLOBAL TEMPORARY, or SYSTEM TABLE (system tables are only displayed if the System check box is selected), Owner name, Last Compiled timestamp, External and Readonly boolean values, Class Name, Extent Size, the name of the Child Table(s) and/or the Parent Table (if relevant) and one or more References fields to other tables (if relevant), whether it uses the %Storage.Persistent default storage class, whether it Supports Bitmap Indexes, the RowID field name, a list of the fields that RowId is based on (if relevant), and whether the table is sharded. If there is an explicit shard key, it displays the shard key fields.
Class Name is a link to the corresponding entry in the InterSystems Class Reference documentation. The Class Name is a unique package.class name derived from the table name by removing punctuation characters, as described in Identifiers and Class Entity Names.
References only appears in Table Info if there is one or more references from a field in the current table to another table. These references to other tables are listed as links to the Table Info for the referenced table.
Sharded: if the table is a shard-master table, the Table Info displays the name of the shard-local class and table with a link to the corresponding entry in the class documentation. If the table is a shard-local table, the Table Info displays the name of the shard-master class and table with a link to the corresponding entry in the class documentation. Shard-local tables are only displayed if the System check box is selected.
This option also provides a modifiable value for the Number of rows to load when table is opened. This sets the maximum number of rows to display in Open Table. The available range is from 1 to 10,000; the default is 100. The Management Portal corrects a value outside the available range to a valid value: 0 corrects to 100; a fractional number rounds up to the next higher integer; a number greater than 10,000 corrects to 10,000.
Fields: a list of the fields (columns) in the table showing: Field Name, Datatype, Column #, Required, Unique, Collation, Hidden, MaxLen, MaxVal, MinVal, Stream, Container, SQLType, Reference To, Version Column, Selectivity, Outlier Selectivity, and Average Field Size.
Maps/Indices: a list of the indexes defined for the table showing: Index Name, SQL Map Name, Columns, Type, Block Count, Map Inherited, and Global.
Index Name is the index property name and follows property naming conventions; when generated from an SQL index name, punctuation characters (such as underscores) in the SQL index name are stripped out. The SQL Map Name is the SQL name for the index. A generated SQL Map Name is the same as the Constraint Name, and follows the same naming conventions (described below). Columns specifies a field or a comma-separated list of fields specified for the index; it may specify the index collation type and full schema.table.field reference, as in the following example: $$SQLUPPER({Sample.People.Name}). Type can be one of the following: Bitmap Extent, Data/Master, Index (standard index), Bitmap, or Bitslice index, and the Unique constraint. The Block Count contains both the count and how that count was determined: set explicitly by the class author (Defined), computed by TuneTable (Measured), or estimated by the class compiler (Estimated). If Map Inherited? is Yes, this map was inherited from a superclass. Global is the name of the subscripted global containing the index data. The naming conventions for index globals are described in Index Global Names. You can supply this global name to ZWRITE to display the index data.
This option also provides a link for each index to rebuild the index.
Maps/Indices: a list of the indexes defined for the table showing: Index Name, SQL Map Name, Columns, Type, Block Count, Map Inherited, and Global.
Index Name is the index property name and follows property naming conventions; when generated from an SQL index name, punctuation characters (such as underscores) in the SQL index name are stripped out. The SQL Map Name is the SQL name for the index. A generated SQL Map Name is the same as the Constraint Name, and follows the same naming conventions (described below). Columns specifies a field or a comma-separated list of fields specified for the index; it may specify the index collation type and full schema.table.field reference, as in the following example: $$SQLUPPER({Sample.People.Name}). Type can be one of the following: Bitmap Extent, Data/Master, Index (standard index), Bitmap, or Bitslice index, and the Unique constraint. The Block Count contains both the count and how that count was determined: set explicitly by the class author (Defined), computed by TuneTable (Measured), or estimated by the class compiler (Estimated). If Map Inherited? is Yes, this map was inherited from a superclass. Global is the name of the subscripted global containing the index data. The naming conventions for index globals are described in Index Global Names. You can supply this global name to ZWRITE to display the index data.
This option also provides a link for each index to rebuild the index.
Triggers: a list of the triggers defined for the table showing: Trigger Name, Time Event, Order, Code.
Constraints: a list of the constraints for fields of the table showing: Constraint Name, Constraint Type, and Constraint Data (field name(s) listed in parentheses). Constraints include primary key, foreign key, and unique constraints. A primary key is, by definition, unique; it is only listed once. This option list constraints by constraint name; a constraint involving multiple fields is listed once with Constraint Data displaying a comma-separated list of the component fields. The Constraint Type can be UNIQUE, PRIMARY KEY, Implicit PRIMARY KEY, FOREIGN KEY, or Implicit FOREIGN KEY.
You can also list constraints by invoking INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. This list constraints by field name. The following example returns the name of the field and the name of the constraint for all UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK constraints:
```
SELECT Column_Name,Constraint_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_SCHEMA='Sample' AND TABLE_NAME='Person'
```
If the table is defined with %PUBLICROWID and no explicit primary key is defined, the RowID field is listed with a Constraint Type of Implicit PRIMARY KEY with the Constraint Name RowIDField_As_PKey.
For explicit constraints, the Constraint Name is generated as follows:
- Constraint specified in the field definition: For example, FullName VARCHAR(48) UNIQUE or FullName VARCHAR(48) PRIMARY KEY. The Constraint Name value for the field is a generated value with the syntax TABLENAME_CTYPE#, where CTYPE is UNIQUE, PKEY, or FKEY, and # is a sequential integer assigned to unnamed constraints in the order specified in the table definition. For example, if FullName has the 2nd unnamed unique constraint (excluding the ID field) in the MyTest table, the generated Constraint Name for FullName would be MYTEST_UNIQUE2; if FullName is the primary key and the 3rd unnamed constraint (excluding the ID field) specified in the MyTest table, the generated Constraint Name for FullName would be MYTEST_PKEY3.
- CONSTRAINT keyword named constraint clause: For example, CONSTRAINT UFullName UNIQUE(FirstName,LastName) or CONSTRAINT PKName PRIMARY KEY(FullName)), the Constraint Name is the specified unique constraint name. For example, FirstName and LastName in the MyTest table would each have the Constraint Name UFullName; FullName would have the Constraint Name PKName.
- Unnamed constraint clause: For example, UNIQUE(FirstName,LastName) or PRIMARY KEY (FullName). The Constraint Name value is a generated value with the syntax TABLENAMECType#, where CType is Unique, PKey, or FKey, and # is a sequential integer assigned to unnamed constraints in the order specified in the table definition. For example, if FirstName and LastName have the 2nd unnamed unique constraint (excluding the ID field) in the MyTest table, the generated Constraint Name for FirstName and LastName would be MYTESTUnique2; if FullName is the primary key and the 3rd unnamed constraint (excluding the ID field) specified in the MyTest table, the generated Constraint Name for FullName would be MYTESTPKey3. (Note mixed uppercase/lowercase and absence of an underscore.)
If a field is involved in more than one uniqueness constraint, it is listed separately for each Constraint Name.
Cached Queries: a list of the cached queries for the table showing: Routine name, Query text, Creation Time, Source, Query Type.
Table’s SQL Statements: a list of the SQL Statements generated for this table. Same information as namespace-wide SQL Statements display.

Catalog Details for a View

Management Portal SQL interface also provides Catalog Details for views, procedures, and cached queries:

The following Catalog Details options are provided for each view:

View Info: Owner name, Last Compiled timestamp. This timestamp updates when you use the Edit View link and save changes.
Defined as Read Only and View is Updateable booleans: if view definition included WITH READ ONLY, these are set to 1 and 0 respectively. Otherwise, if the view is defined from a single table they are set to 0 and 1; if the view is defined from joined tables they are set to 0 and 0. You can change this option using the Edit View link.
Class Name is a unique package.class name derived from the view name by removing punctuation characters, as described in Identifiers and Class Entity Names.
Check Option is only listed if the view definition included the WITH CHECK OPTION clause. It can be LOCAL or CASCADED. You can change this option using the Edit View link.
Class Type is VIEW. It provides an Edit View link to edit the view definition.
View Text is the SELECT statement used to define the view. You can change the view definition using the Edit View link.
The list of fields includes the Field Name, Data Type, MAXLEN Parameter, MAXVAL Parameter, MINVAL Parameter, BLOB (%Stream.GlobalCharacter or %Stream.GlobalBinary field), Length, Precision, and Scale.
View’s SQL Statements: a list of the SQL Statements generated for this view. Same information as namespace-wide SQL Statements display.

Catalog Details for a Stored Procedure

The following Catalog Details options are provided for each procedure:

Stored Procedure Info:
Class Name is a unique package.class name derived from the procedure name by pre-pending a type identifier ( ‘func’, ‘meth’, ‘proc’, or ‘query’) to the class name (for example, the SQL function MyProc becomes funcMyProc) and removing punctuation characters, as described in Identifiers and Class Entity Names. Class Document is a link to the corresponding entry in the class documentation. Procedure Type (for example, function). Method or Query Name the name of the generated class method or class query; this name is generated described in Identifiers and Class Entity Names. The Run Procedure link provides an option to interactively run the procedure.
Stored Procedure’s SQL Statements: a list of the SQL Statements generated for this stored procedure. Same information as namespace-wide SQL Statements display.

Catalog Details for a Cached Query

Cached Query provides the full text of the query, an option to show the query execution plan, and an option to interactively execute the cached query.

Wizards

Filtering Schema Contents