Enumerating SQL Compact Tables, Columns, Constraints and Indexes

Now that you know how to enumerate tables and columns, it's fairly easy to enumerate all the other schema elements: indexes, primary keys, unique constraints and foreign keys. Interstingly both primary keys and unique constraints are implemented as indexes and you list them all through the INFORMATION_SCHEMA.INDEXES view (I'm omitting the prefix from now on). This view will tell you immediately if an index is the table's primary key, but will not tell you if you are looking at an unique constraint. To check that, you must take a look at the TABLE_CONSTRAINTS view.

Foreign keys are a bit different: you must know what table they refer to (through a primary key, unique index or unique constraint), what columns are mapped and what are the update and delete rules. You get some of this information from the REFERENTIAL_CONSTRAINTS view, but you must complement it using the KEY_COLUMN_USAGE view.

You can see this mechanism at work on the sample code's updated SqlCeTable.LoadSchema method. Note that I kept the piece-wise loading mechanism in order to make things work faster. Alternatively you could devise an alternative schema loading mechanism using a DataSet to store all the data and then query it appropriately. Unfortunately this will imply a performance penalty if you port this code to a mobile device.

Now that we have a simple way to load and query the database schema, we can widen our horizons a bit and start extracting more useful information like scripts. Stay tuned for the next episodes...

Sample code: SchemaTree2.zip

Windows Phone 7 Development Videos from TechEd 2010

Here are a couple of videos from TechEd 2010 North America on how to develop Silverlight applications for Windows Phone 7:
An In-Depth View of Building Applications for Windows Phone 7 with Microsoft Silverlight (Part 1)
An In-Depth View of Building Applications for Windows Phone 7 with Microsoft Silverlight (Part 2)

You can follow the full Windows Phone track here.

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