Repository SQL Schema

See Also

The Repository SQL schema consists of a standard schema and an extended schema. The standard schema consists of tables that contain the core information needed to manage all Repository objects, relationships, and collections. The standard schema also contains tables that are used by the Repository to store the definition information for information models.

SQL schema extensions are automatically generated by the Repository engine when you create or extend an information model. Normally, one table is created for each interface that is defined in the Repository. The table contains the instance data for persistent properties that are attached to the interface. One column in the table is created for each property. If an interface is defined that has no member properties, a table is not created.

Although the extended schema is automatically generated, you can tune the extended schema to optimize performance and data retrieval, if you are an experienced SQL administrator. For example, by default, the properties of each interface are stored in a separate SQL table. You can map the properties of multiple interfaces to a single table. You can also specify the column names and data types to be used for property data. You can add indexes to tables, but you must not remove indexes that have been automatically defined by the Repository.

You can construct an SQL query to extract specific information from the Repository. You need to be familiar with the SQL schema to build such a query. The set of SQL tables that comprise the standard schema is shown below.

SQL table name Description

RTblClassDefs Contains information about classes that are defined in the Repository.
RTblRelColPairs Contains information about collections.
RTblDatabaseVersion Contains the version and the build of the engine that created the Repository database.
RTblIfaceDefs Contains information about interfaces that are defined in the Repository.
RTblPropDefs Contains information about property definitions.
RTblProps Contains property values for annotational properties that are attached to Repository objects.
RTblRelshipProps Contains property values for annotational properties that are attached to relationships.
RTblRelships Contains information about relationships.
RTblRelColDefs Contains information about collection types.
RTblRoot Contains the internal identifier of the Repository root.
RTblSites Translates local site identifiers to global site identifiers.
RTblTypeLibs Contains information about Repository type libraries.
RTblVersions Contains information about Repository object versions.
RTblWorkspaceItems Contains information about the inclusion of object versions in workspaces.

Microsoft Repository can access Repository databases that are managed by either Microsoft Jet or Microsoft SQL Server. Since the underlying database management system (DBMS) can vary, the utilities and tools that you use to administer the Repository database (at the database level) also vary. For example, if your Repository database is damaged due to a power outage or system failure, you should use the recovery tools that are provided with your DBMS to repair the damage. Similarly, if your Repository database requires periodic defragmentation, you should use the defragmentation tools that are provided with your DBMS.