sql server - "If not exists" fails on SQL CE

Question

I've got an unexpected problem. I'm making a script to update the schema on a SQL CE database. This won't run:

if not exists
(
    Select column_name from information_schema.columns
    where column_name = 'TempTestField' 
        and table_name = 'Inventory_Master_File' 
)
    Alter table Inventory_Master_File
      add TempTestField nvarchar(10) null   

I think this is because I'm using stuff that isn't supported in SQL CE. Anyone know how to do this? I've tried rearranging the script and can't get anything to work. I tried "alter table ... where not exists ...".

Note that the "select" part runs fine and also the "alter" part. The problem is "if not exists". I know there are some other postings regarding problems like this using SQL CE but I couldn't find an answer to this particular problem.

Cheers
Mark

UPDATE:

I've spent over an hour looking for a solution. I've found many postings asking for help with similar problems but I've still got no idea how to fix it. I really don't want to do this in C# code. I need to do this in a SQL script. I can't believe something this basic is causing so much difficulty :(

Answer 1

It appears that SQL CE does not support any procedural extensions at all; only DDL and DML like from the 1970s.

You will either have to put procedural logic in your application (C# or whatever) or go to an embeddable database with procedural extensions, like SQLite.