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