INFO: System Tables in SQL Server CE Database (272210)

SUMMARY

You can run the following command to get a list of the system tables in a SQL CE database:

SELECT *  FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE='SYSTEM TABLE'

You can query any user or system table; however, you cannot update system tables as is possible in other editions of Microsoft SQL Server. System table names are also not identical between the Microsoft SQL Server 2000 Windows CE Edition and other editions of Microsoft SQL Server.

MORE INFORMATION

WARNING: The information contained in this article is subject to change at any time. To maintain future compatibility, your applications must not query system tables directly. Instead, use the Information Schema Views, which are discussed later in this article.

Every database contains the following system tables:

MSysObjects
MSysConstraints

If you are using standard replication, the following replication system tables are created:

msmerge_replinfo
sysmergearticles
msmerge_tombstone
msmerge_genhistory

Each replicable user table created on your device has these three additional columns added:

s_Generation
s_RowLineage
rowguid

s_Generation and s_RowLineage are replication system columns added by SQL CE. These columns are used in tracking the changes made at Subscribers. These columns are found only on SQL CE subscribers.

s_Generation tracks the state of the changed row, so that only rows that have been changed since the last merge are sent back to a merge partner.

s_RowLineage tracks which subscriber (or in some cases, the publisher) made a change.

If you are using RDA, you will see a MSysRDATombStone system table, as well.

Specific permissions are not implemented by the SQL CE engine. There is a database password feature that allows you to protect the database file; however, SQL CE does not support object-level security.

SQL CE allows you to use the INFORMATION_SCHEMA view to retrieve information about system tables just like other editions of Microsoft SQL Server. Use the INFORMATION_SCHEMA views to retrieve information in and about your SQL CE system tables instead of attempting to use the system table names for other editions of Microsoft SQL Server. Most scripts written to directly access system tables in Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 fail if run against SQL CE.