0

If I run the following SQL in SQL Server CE,

alter table audit add new_key nvarchar(100) null

Can that be changed to check whether the column exists before trying to add it in the same SQL statement? I am aware you can do that quite easily in SQL Server, but SQL Server CE?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
peter
  • 13,009
  • 22
  • 82
  • 142

2 Answers2

2

If you are looking for something like you can do with SQL server: IF NOT EXISTS (..., unfortunately, this is not possible with SQL Server Compact (the T-SQL IF is not supported). See SQL Reference (SQL Server Compact).

Based on this thread, you can do something like this:

select * from Information_SCHEMA.columns
where Table_name='audit' and column_name='new_key'

(ie no result = does not exist).

Also note that batch queries are not supported in SQL Server Compact at all. So you can't check if the column exists and add the column if needed in the same SQL statement.

Or if you are looking for a snippet that you can use with VB.NET or C#, take a look at this SO question: How can I determine whether a column exists in a SQL Server CE table with C#?

Community
  • 1
  • 1
Chris
  • 8,527
  • 10
  • 34
  • 51
-2

The easiest way would be to query information_schema and see if the column is in there.

IF NOT EXISTS(
    SELECT * FROM information_schema.columns 
    WHERE Table_Schema = 'dbo'
    AND Table_Name = 'audit'
    AND Column_Name = 'new_key'
) BEGIN
  alter table audit add new_key nvarchar(100) null
END
NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Actually, there is an easier way. Run the alter table query. It will either add the column or throw an error explaining why it didn't. It's not as if adding columns is a regular event. – Dan Bracuk Sep 30 '13 at 22:48
  • 2
    OK, but I am talking about SQL CE, the query you provided doesn't work with SQL CE. – peter Sep 30 '13 at 23:00
  • Although I'm technically wrong (IF not being supported in SQL CE); I'm leaving the answer as Dan is correct in that you should just go ahead and run the alter statement then catch the error if the column already exists. No real point in testing it before hand. – NotMe Jan 05 '14 at 00:21