Repository SQL Schema
The repository SQL schema is a mapping of information model elements to SQL schema elements. The repository engine uses data in these tables to instantiate and manage COM objects. 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 Microsoft® SQL Server™ 2000 Meta Data Services to store the definition information for information models. Standard schema tables are prefixed with RTbl.
If you obtained Meta Data Services through SQL Server, repository SQL schema tables are located in the msdb system database.
- The extended schema consists of tables that are automatically generated by the repository engine when you create or extend an information model. An interface is mapped to at most one table in a repository database. 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, no table is created.
Adding Data to Repository SQL Schema
You can add data to the repository SQL schema when you install an information model or create an information model programmatically. When you use a SQL Server database for your repository storage, the repository engine creates stored procedures to insert the data. For more information about how these stored procedures are named, see Naming Stored Procedures.
Tuning the Extended Schema
Although the extended schema is automatically generated, experienced model designers can tune the extended schema to optimize performance and data retrieval. 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 Meta Data Services. For more information, see Tuning the Database Schema of an Information Model.
Querying the Repository
You can construct an SQL query to extract specific information from a repository. Although it is simpler to perform queries through generated views, you can manually build an SQL query against the repository SQL schema if you want a result set that covers more than one information model. To build such a query, you must be familiar with the repository tables. For more information about querying, see Repository SQL Tables and Generating Views.