18

I know this is similar to this question, but I'm using SQL Server CE 3.5 with a WinForms project in C#. How can I determine whether a table exists? I know the IF keyword is not supported, though EXISTS is. Does information_schema exist in CE where I can query against it? Thanks.

Community
  • 1
  • 1
Michael Itzoe
  • 1,949
  • 4
  • 29
  • 49
  • I thought I had a nifty implementation of just what code was needed for this, but it's not working for me. I posted a question diesbezueglich at: http://social.msdn.microsoft.com/Forums/en-US/8ee6aefa-e47b-4f0c-8735-120b8bf041ec/why-is-my-sqlserver-ce-code-failing (I tried to post it on codeproject, but it froze on me multiple times) – B. Clay Shannon-B. Crow Raven Jul 31 '13 at 19:40
  • Third time was a charm: http://www.codeproject.com/Questions/629607/Why-is-my-SQLServer-CE-code-failing Yes, I cross-posted, but to two different forums, so hopefully that is not considered gauche. – B. Clay Shannon-B. Crow Raven Jul 31 '13 at 19:43

3 Answers3

36

Yes, it does exist:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TableName'
Mehrdad Afshari
  • 414,610
  • 91
  • 852
  • 789
1

As an alternative you can Query the Table and catch the Exception thrown. If there is an Exception, the Table wasn't found, else the Table exists.

SELECT TOP 1 1 FROM TableName;

A little and simple Performance Test had better Results than the Query against INFORMATION_SCHEMA. Although I would consider a Query against INFORMATION_SCHEMA as cleaner.

chrisgl
  • 44
  • 2
  • If the table doesn't exist, that method will throw an exception, so you shouldn't do that - catching an exception is never the way to go when there is a clean alternative. – Rob King May 13 '15 at 15:08
0

Using Database helper:

var db = Database.Open("MyDatabase");
var sql = @"SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTable'"
var count = db.QueryValue(sql);
if(count.Equals(1)){
    //table exists
}
Ghasem
  • 14,455
  • 21
  • 138
  • 171