An enterprise geodatabase is made up of system tables, procedures, and functions that are stored in a database management system. The system tables prefixed with GDB_ describe the geodatabase schema that specifies dataset definitions, rules, and relationships. These system tables contain and manage metadata required to implement geodatabase properties, data validation rules, and behaviors. Tables are as follows:
- GDB_Items: Contains a listing of all items contained within a geodatabase such as feature classes, topologies, and domains
- GDB_ItemTypes: Contains a predefined list of recognized item types, such as Table
- GDB_ItemRelationships: Contains schema associations between items such as which feature classes are contained within a feature dataset
- GDB_ItemRelationshipTypes: Contains a predefined list of recognized relationship types such as DatasetInFeatureDataset
The GDB_Items and GDB_ItemRelationships tables represent the user-defined schema within a particular geodatabase. The contents of the tables are modified as the schema of the geodatabase is modified. The GDB_ItemTypes and GDB_ItemRelationshipTypes tables are static. The following diagram illustrates the relationships between these four tables:
GDB_Items table
A geodatabase is a container of geographic and nongeographic items. These items include tabular datasets, such as feature classes and tables; dataset containers, such as feature datasets, topologies, and geometric networks; datasets that control more advanced geodatabase behavior; and other items such as domains, toolboxes, and workspace extensions. All these items are stored within the geodatabase in the GDB_Items table, where each item is stored as a single row.
An item has the following attributes:
- UUID: A unique identifier for the item
- Name: The name of the item
Be aware that an item's name is not guaranteed to be unique; for example, a relationship class and feature class can have the same name. However, it is not recommended that you give items the same name, since item names should be descriptive enough to differentiate what the item contains.
- Type: A UUID representing the type of item in the record; this corresponds with the unique identifier.
- Definition: An XML document that defines the properties of an item; these properties and the XML schema vary based on item type. For example, the definition of an object class contains information such as subtypes, whereas the definition of a coded value domain contains code/value pairs, and the definition of a relationship class contains (among other things) relationship rules.
- Documentation: An XML document storing the item's metadata
- Properties: An integer value that can be used with bitmasks to retrieve several Boolean properties of the item. Currently, this is only used to indicate whether an item is visible in ArcGIS Desktop.
- Shape: The extent of the dataset as a polygon if applicable (the data type depends on the underlying DBMS).
These seven attributes are those you are most likely to access using SQL.
The next attributes store information from an item's definition (such as a feature class's geometry type), which are duplicated outside the XML Definition to allow more efficient browsing of the geodatabase. Examples of these are the Dataset Subtype and Dataset Info properties, the codes of which are dependent on the item's type. These attributes are described as follows:
- Physical Name: The fully qualified name of the item
- DatasetSubtype1: For feature classes and raster catalogs, DatasetSubtype1 stores the feature type of the table. For topologies, the topology ID is stored.
- DatasetSubtype2: For feature classes and raster catalogs, DatasetSubtype2 stores the geometry type. For relationship classes, it stores a value indicating whether the relationship is attributed or nonattributed.
- DatasetInfo1: Stores the name of the shape field for feature class items
- DatasetInfo2: Stores information for feature classes that participate in topologies
- Path: The unique relative path to the item
- URL: The associated URL for the item; used with catalog services
- Defaults: Item metadata; not used directly by the geodatabases, but used by other clients, such as ArcIMS metadata services
- ItemInfo: Storage information for the item, such as symbology, that is independent of the underlying dataset
GDB_ItemTypes table
The GDB_ItemTypes table contains the predefined set of recognized item types in the geodatabase. Item types are used to normalize the GDB_Items table (the type of an item in the GDB_Items table is represented as a UUID), to provide better support for future geodatabase functionality enhancements and define a type hierarchy. All parent types are abstract; therefore, parent items will not actually be found in the GDB_Items table.
The contents of the GDB_ItemTypes table are static; it is not modified as items are created or deleted.
An item type has the following attributes:
- UUID: A unique identifier of an item type and the foreign key to the GDB_Item table's type column
- ParentTypeID: The item type's parent type
In the case of the abstract item type (which has no parent), this is a null UUID.
- Name: The name of an item type such as Feature Class or Range Domain
- ObjectID: The unique identifier for the GDB_ItemTypes table
GDB_ItemRelationships table
Nearly all geodatabase items have relationships with at least one other geodatabase item. For example, a stand-alone table is related to the root folder of a geodatabase, a domain is related to the feature classes or tables that rely on it for validation, and feature classes are related to the topology in which they participate. The GDB_ItemRelationships table is used to track these relationships.
Item relationships have the following attributes:
- UUID: A unique identifier of an item relationship
- Type: A UUID representing the relationship's type; corresponds with the unique identifier of the item relationship's type
- OriginID: The unique identifier of the relationship's origin item
- DestinationID: The unique identifier of the relationship's destination item
- Attributes: An XML document containing detailed information about the item relationship
Many relationship types do not include attributes; they are only used in some relationships between controller datasets and their controlled datasets.
- ObjectID: Unique identifier
- Properties: An integer value that can be used with bitmasks to retrieve several Boolean properties of the item. Currently, this is only used to indicate whether the items participating in the relationship are visible in ArcGIS Desktop.
You would likely only access the first five attributes using SQL.
GDB_ItemRelationshipTypes table
In the same way that a static collection of item types is used to classify items, a static collection of item relationship types is used to classify item relationships. For example, DatasetInFeatureDataset (indicating that a feature class, relationship class, or controller dataset exists inside of a feature dataset) and DomainInDataset (indicating that a dataset uses a domain for class-level or subtype-level validation) are two types of item relationships. Unlike item types, item relationship types do not have a hierarchy concept with parent and child types.
Item relationship types have the following attributes:
- UUID: A unique identifier of an item relationship type and the primary key for the item relationship table's type column
- Name: The name of the item relationship type, such as DatasetInFolder
- Forward Label: A description of the relationship from the context of the origin item
- Backward Label: A description of the relationship from the context of the destination item
- Origin Item Type ID: The UUID of the item type that can act as the origin item in item relationships of this type
- Destination Item Type ID: The UUID of the item type that can act as the destination item in item relationships of this type
- Is Containment: Indicates whether the destination item can exist even if the origin item is deleted.
XML in geodatabase system tables
As mentioned in the preceding sections, some of the fields use an XML data type. In geodatabases in IBM DB2, Microsoft SQL Server, and PostgreSQL, these columns use the native XML of the database management system and, therefore, can be queried using XPath expressions with SQL.
In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML, which stores information as BLOBs in a series of separate tables. As such, they cannot be directly accessed with SQL.
To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOB columns. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB column. The contents of the CLOB columns can be read as text.
When querying these views, you extract the string from the CLOB column, convert it to an XML type, and execute an XPath query on it. Be aware that if you attempt to create an XML type for a column that contains a blank string, it will fail with the message XML parsing failed because Oracle does not support this.
To query the contents of the CLOB columns, you must configure the Oracle server to accept SQL connections. See Configure the Oracle extproc to access the geodatabase with SQL for more information.
How GDB_ tables are related
The best way to see how the system tables that track geodatabase functionality work together is to examine them using SQL. Selecting all rows and columns from the GDB_Items table returns a result set similar to the following (along with many other fields):
Notice that the values in the Type column are stored as UUIDs. These UUIDs can be resolved using the GDB_ItemTypes table, as shown in the following query:
SELECT gdb_items.UUID, gdb_itemtypes.name AS "Type", gdb_items.name
FROM gdb_items INNER JOIN gdb_itemtypes
ON gdb_items.type = gdb_itemtypes.UUID;
This returns a similar result as a simple SELECT statement run on the GDB_Items table but with the UUIDs in the Type column replaced with human-readable strings.
Similarly, the GDB_ItemRelationships table also contains UUIDs that are key values from other tables. Each relationship contains two of the UUID values seen in the GDB_Items table above: one for the relationship's destination item and one for its origin item. To resolve these to human-readable strings, you must join the GDB_ItemRelationships table to the GDB_Items table twice as follows:
--SQL Server and PostgreSQL query
SELECT relationships.type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
gdb_itemrelationships AS relationships,
gdb_items AS dest_items
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid;
--Oracle query
SELECT relationships.type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
sde.gdb_itemrelationships relationships,
sde.gdb_items dest_items
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid;
The following illustration is an example of a result set that could be returned from the previous query:
Although this shows the relationships between items in the geodatabase, the relationship type is also needed. You can resolve the Type UUID by expanding the previous query to include a join with the GDB_ItemRelationshipTypes table as follows:
--SQL Server and PostgreSQL query
SELECT reltypes.name AS type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
gdb_itemrelationships AS relationships,
gdb_items AS dest_items,
gdb_itemrelationshiptypes AS reltypes
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid AND
relationships.type = reltypes.UUID;
--Oracle query
SELECT reltypes.name AS type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
sde.gdb_itemrelationships relationships,
sde.gdb_items dest_items,
sde.gdb_itemrelationshiptypes reltypes
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid AND
relationships.type = reltypes.UUID;
This returns the following:
Common GDB_ table queries
See the following topics for information on common types of SQL queries performed on GDB_ tables: