Enumerating SQL Compact Tables and Columns

Today I'm starting a small series of blog posts where I will show how you can enumerate a SQL Compact database schema using the INFORMATION_SCHEMA views. Let's start with the basic stuff: tables and columns.

Table enumeration is performed by querying the TABLES view, most specifically the TABLE_NAME column. I implemented the new SqlCeSchema class (see sample code) that you can use for this purpose. Instances of this class are created with a reference to a SqlCeConnection object and you populate the Tables collection by calling the Load method (make sure the connection is open). Note that this operation merely loads the table names and creates empty SqlCeTable objects. To retrieve the column information for each table, you must call the SqlCeTable.LoadSchema method.

I designed the code this way in order to avoid long loading times for large databases. This way you can load only the schema for a particular table without the need to load the entire database schema.

You can see this at work in the sample code: it displays a simple tree containing the list of tables in a given database and the column enumeration is requested per table when you expand the table node.

Right now the code is limited to table and some of the column schema. I will be updating this sample in future posts to include more column schama details, indexes and constraints.

Sample code: SchemaTree1.zip