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 Responses to “System Views in SQL Server Compact Edition: Indexes”

  1. Bart Says:

    Any idea how to get the index direction (i.e. ASC or DESC)?

  2. arcanecode Says:

    Thanks for the link Aneesh, but the linked post has to do with indexes in standard SQL Server. Indexes in Compact Edition are different.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: