Microsoft SQL Compact Edition rename column

Question

I am having problems renaming a column in SQL Server Compact Edition. I know that you can rename a table using sp_rename, but this doesn't work with columns.

I've searched for an alternative, but haven't found one.

Can I delete a column and then add a new one after a specific column? If I delete the column and add it after the a specified one the data would be lost right?

It seems that once you have created the table it can't be properly modified - is this another of the limitations of SQLCE?

Solution 1

It does indeed seem that SQL CE wont allow changing column names.

You're on the right track with creating a new column and deleting the old.

If you just add a column and delete the old you will lose the data so you need to issue an update statement to shift the data from the old to the new.

Something along the lines of

alter Table [dbo].[yourTable] add [newColumn] [DataType]

update yourTable set newColumn = oldColumn

alter Table [dbo].[yourTable] drop column [oldColumn]

Should create your new column, duplicate the data from old to new and then remove the old column.

Hope it helps!

Solution 2

sp_rename works with columns too:

EXEC sp_rename
objname = '< Table Name.Old Column Name >',
@newname = '<New Column Name>',
@objtype = 'COLUMN'  

Example:

SP_RENAME 'MyTable.[MyOldColumnName]' , '[MyNewColumnName]', 'COLUMN'  

UPDATE: Actually, The sp_rename procedure is not avialable in SQL CE! You can find the solution at http://www.bigresource.com/Tracker/Track-ms_sql-4Tvoiom3/