How to enumerate the database schema with DesktopSqlCe

This article describes how to enumerate a SQL CE / Mobile / Everywhere database schema using DesktopSqlCe.

Retrieving the database schema may be important for some specific application needs such as determining if a given table name exists or retrieving a given column data type. Everything starts with an instance of the SqlCeDatabase class. You can get a reference to such an object through the SqlCeConnection.SqlCeDatabase property. Schema information is accessed through the SqlCeDatabase.Tables collection. Each item in the collection is of the SqlCeTable type and each table exposes its own schema through the SqlCeTable.Columns, SqlCeTable.Indexes and SqlCeTable.ForeignKeys collections.

Retrieving schema information on a complex database can be a time consuming job, and that is why the SqlCeDatabase.Tables collection is empty when the database is opened. To load this collection with the table list, you must call the SqlCeDatabase.LoadSchema method. Please note that this will only fill in the tables collection, but each SqlCeTable object will not have any other schema information loaded. If you want to enumerate a specific table schema, you must also call the SqlCeTable.LoadSchema method. This load-on-demand strategy (used by SQL CE Console) ensures that the user gets only the schema data that he or she requests and load times are kept to a minimum.

So, in a nutshell, to enumerate the database schema just call LoadSchema on the database object and also for each SqlCeTable object in the SqlCeDatabase.Tables collection.

Final notes

• Primary keys are stored in the SqlCeTable.Indexes collection as a special SqlCeIndex object (PrimaryKey property set to true).
• Unique constraints are enumerated as unique indexes.
• Column defaults use the provider's SQL syntax for evaluation. Use care when applying them to another database.
• For your convenience, all schema objects support the CreateCommand and DropCommand properties. These return the properly formatted SQL commands for creating or dropping the schema element.