Row count and space usage in sql CE databases

Question

Hi I have two DB1.sdf and DB2.sdf file (sql ce). I want to compare the two. I want to check the no of rows and the space used in each of the database.

Is there any way to do that? Also is there any way to to a corresponding table to table comparision in the two databases?

Answer 1

You can use this to try to get row count:

public Int64 GetRowCount(string tableName)
{
   object value = ExecuteScalar("SELECT CARDINALITY FROM INFORMATION_SCHEMA.INDEXES WHERE PRIMARY_KEY = 1 AND TABLE_NAME = N'" + tableName + "'");
    if (value != null)
    {
       return (Int64)value;
    }
    return -1;
 }

If that fails, you can use "SELECT COUNT(*) FROM table"

Space used is not available, but you can estimate based on this: http://support.microsoft.com/kb/827968

For data comparision, the best approach would probably be to migrate each sdf to a SQL Server Express database, and use a SQL Server Data Compare tool