SQL Compact Schemas

Now that I have looked into the most basic principles of using a SQL Compact database from native code (base table cursors and command execution), I will now look at the available tools to enumerate and manipulate the database schema. There are essentially two ways to go about this:

1. SQL commands
2. OLE DB interfaces

Using SQL commands
This may be the easiest approach and is also language-independent - you can use these techniques from both native and managed code. To determine what's in your database you can execute SELECT commands against the INFORMATION_SCHEMA system views. These work like regular SELECT commands and return specific rowsets:

• COLUMNS
• INDEXES
• KEY_COLUMN_USAGE
• PROVIDER_TYPES
• TABLES
• TABLE_CONSTRAINTS
• REFERENTIAL_CONSTRAINTS (not documented for 2.0)
  

To retrieve information from one of these views, you just execute a simple query like:

SELECT * FROM INFORMATION_SCHEMA.TABLES

This will return all tables in your database. The advantage of using a SELECT command is that you can add a WHERE clause and filter the result. If you need to make changes to your database schema you can use all the supported DDL commands. Since version 3.0 came out, Microsoft even provided us with a stored procedure to rename tables: sp_rename. Unfortunately you cannot rename columns which sometimes is needed.

Using OLE DB interfaces
All of this (and a bit more) can also be done through some of the exposed OLE DB interfaces, namely the following Session interfaces:

• IDBSchemaRowset - Generates rowsets that are similar to the ones returned by the INFORMATION_SCHEMA views.
  
• IIndexDefinition - Creates and drops indexes.
  
• ITableCreation - Retrieves the table definition (full schema).
  
• ITableDefinition - Creates drops tables, adds and drops columns.
  
• ITableDefinitionWithConstraints - Same as above, but also manages table constraints.
  
• IAlterIndex - Only renames indexes in SQL Compact.
  
• IAlterTable - Allows changing table properties like the table name, column defaults and identity properties. You can also use this interface to rename columns (not available in managed code).
  

Note that when using these interfaces, you are accessing the storage engine directly, circumventing the Query Processor.

On my next post I'm planning to publish a sample that illustrates how to use these interfaces.