- SQL commands
- OLE DB interfaces
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:
- REFERENTIAL_CONSTRAINTS (not documented for 2.0)
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).
On my next post I'm planning to publish a sample that illustrates how to use these interfaces.