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.
Arcane Code 