System Views in SQL Server Compact Edition: Indexes

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.


3 thoughts on “System Views in SQL Server Compact Edition: Indexes

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s