The last view we’ll discuss is the INFORMATION_SCHEMA.PROVIDER_TYPES. Strictly speaking, this does not provide a view into your particular database, but into SQL Server Compact Edition. As such, it probably won’t be very useful in day to day queries into the database.

What it may be useful for is in automating database code generation. This table lists all of the valid database types that SQL Server CE supports. Presumably if Microsoft chooses to add new data types to SSCE they will show up here.

Let’s take a look at the query that’ll return some useful info, and what each field means.

select type_name, data_type, column_size,

       literal_prefix, literal_suffix,

       is_nullable, case_sensitive,

       unsigned_attribute, fixed_prec_scale,

       minimum_scale, maximum_scale, is_fixedlength

 from information_schema.provider_types

type_name contains the list of valid datatypes, such as int, real, nvarchar, etc.

data_type contains a numeric representation of the type of data stored in the field. Note this is not unique, for example nchar and nvarchar, and ntext all have the same data_type of 130.

Column_size indicates the max size for a data_type.

Literal_prefix / literal_suffix indicates what you should use to wrap a literal value. This is probably one of the more useful items in this table. Let’s say you have a static text value you want to insert into a SQL statement, perhaps a system name such as the word arcanecode.com. Looking up the literal_prefix for a ntext item I see that it is N’ (the letter N followed by a single quote). The literal_suffix is just a single quote mark, so to include in a SQL statement I’d need to put N’arcanecode.com’ .

Is_nullable is obvious, a 1 indicates the field is allowed to hold null values, a 0 means it can’t. As of this writing, all the field types in SSCE are nullable.

Case_sensitive is similar, 1 shows the field is case sensitive, 0 not. Again, as of this writing none of the fields in SSCE are case sensitive.

Unsigned_attribute is null for non-numeric type, but for numbers a 1indicates the data_type is unsigned only (i.e. no negative values allowed). A 0 indicates negative values are valid.

Fixed_prec_scale is another Boolean field. A 1 indicates the precision (the number of positions after the decimal point) is fixed. A 0 indicates you can change the precision.

Minimum_scale, maximum_scale is tied to the fixed_prec_scale. For items like int’s where the precision is always the same, this is null. However on numerics the user (i.e. you) can set the scale, and these fields indicate the valid range.

Is_fixedlength is a Boolean that flags whether the data_types length is fixed. For numerics this is going to be 1 (true), and for most text types this is 0 (false).

If nothing else this table makes a great quick reference. Since SSCE only supports a subset of the SQL Server data types, it’s handy to have a place to see which types are supported.

In your application, you could use this to build code generators or build SQL statements to create new databases. For example you could use it to look up the prefix / suffix types for a data_type.

And there you go, this completes a week long look at the various views built into SQL Server 2005 Compact Edition, and how you can use them within your applications.

Constraints are actually found in two views, INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE. Let’s look at table constraints first.

select constraint_name, table_name, constraint_type

  from information_schema.table_constraints;

This returns a simple list of the names of the constraints for each table with a constraint in your system. If the table does not have a constraint, it won’t be found here. Finally, the constraint type will either read “PRIMARY KEY” or “FOREIGN KEY”.

To gather more info about constraints, we have to shift to the key_column_usage view. This view shows the fields associated with a constraint.

select constraint_name, table_name, column_name, ordinal_position

  from information_schema.key_column_usage;

select kcu.constraint_name, tc.constraint_type, kcu.table_name,

       kcu.column_name, kcu.ordinal_position

  from information_schema.key_column_usage kcu,

       information_schema.table_constraints tc

 where kcu.constraint_name = tc.constraint_name;

Knowing what indexes are available can help you speed access to your database. While the indexes view has a lot of fields, there’s only a handful that are really useful to us.

select table_name, index_name, primary_key,

       [unique], ordinal_position, column_name

  from information_schema.indexes;

Table_name and column_name are obvious, as is the index_name field. The primary_key will be 1 if this is a primary key index. The same logic applies to unique, 1 for unique fields. Also note that since unique is a reserved word, we had to enclose it in brackets in order to use it in our query.

That just leaves the ordinal_position, which has the same use here as it did in the columns view in yesterday’s post. It indicates the order the fields occur in the index.

Indexes are pretty simple, but it’s handy to be able to determine what you have available.

Today we’re going to explore a view that is quite a bit more useful. First though, I spent a little time rewriting the create table scripts so we could fully explore a lot of the features these tables and SSCE has to offer.

Drop the two tables you created yesterday, or just create a new database. Then you can run these statements:  

create table employee (

empid uniqueidentifier not null,

last   nvarchar(100),

first nvarchar(100),

birth  datetime,

constraint pk_empid primary key (empid)

);

create table parts (

partid bigint IDENTITY(100,1) not null,

partname nvarchar(100) not null,

qtyonhand int default 0,

constraint pk_parts primary key (partid)

);

create table inventory (

partid bigint not null,

binid bigint not null,

location nvarchar(50),

constraint pk_inventory primary key (partid, binid),

constraint fk_part foreign key (partid) references parts(partid)

);

As you can see, the new version of parts uses a bigint for the primary key, and I’ve set it up as an identity column. The 100, 1 after identity tells SSCE to start the first number at 100, and increment by 1.

For use in a later example I’ve also redone the inventory table and added a part table. I’ve made the index for the inventory table two parts, and added a foreign key back to the parts table. Now let’s take a look at the Columns view.

Like many of the views, a simple select * is going to return a lot of empty fields. Thus I have created the following statement which returns the most useful fields to us.  

select table_name, column_name, ordinal_position,

       column_hasdefault, column_flags, is_nullable, data_type,

       character_maximum_length, numeric_precision, numeric_scale,

       datetime_precision, autoinc_min, autoinc_max, autoinc_next,

       autoinc_seed, autoinc_increment

  from information_schema.columns

I won’t go over every single field, but let me point out a few of the more useful ones. The table_name, column_name, and data_type fields are pretty obvious and are probably the ones you’ll use the most.

Column_hasdefault is a Boolean, 1 indicates there’s a default value, 0 indicates there’s no default. Character_maximum_length is just as it describes, for character types it indicates the max length, for non characters it will be null.

The autoinc_* fields are only valid for ints or bigints being used as identity columns, such as the partid field in the parts table. Min and max are obvious, describe the bounds of the field. Seed simply shows what the starting value is. This can be handy if you want to know what value to start reading from. Increment shows the value to add when creating the next primary key.

The most handy though is Next. Next will show you what the next primary key will be. This can be useful if the user is creating a new record, and you’d like to go ahead and show them what the new primary key will be without wanting to create the record.

Finally is ordinal_position. This field indicates the order the fields occur in the table. Use this for sorting, if you want to display the fields in the same order as the database: order by ordinal_position.