How to determine if a table exists in SQL Server Compact (SSC) Edition

Monday, March 10th, 2008

In one of my managed code project, I need to determine if a table exists in SSC.  This is pretty easy to do in regular SQL Server with T-SQL:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyTable]') AND type in (N'U'))

However, this does not work in SQL Server Compact Edition.  First of all, SSC does not have sys.objects, second, SSC does not support conditional T-SQL.

After googling a bit, I found the first problem can be solved by using INFORMATION_SCHEMA.TABLES, how about the second problem?  I just could not find any conditional T-SQL reference in SSC.

I ended writing a c# function like this:

public static bool DoesTableExist(string tableName, SqlCeConnection ssceconn)
{
    string exsitTSql = @"SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + tableName + "'";
    int cnt = (int) ExecuteScalar(exsitTSql, ssceconn);
    return (cnt == 1);
}

This entry is filed under Mobile, SQL. You can follow any responses to this entry through the RSS 2.0 feed.You can leave a response, or trackback from your own site.