15

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?

mskfisher
  • 3,291
  • 4
  • 35
  • 48
lucian
  • 201
  • 1
  • 2
  • 9

3 Answers3

16

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!

Robb
  • 3,801
  • 1
  • 34
  • 37
  • 1
    It's possible that this could work, but the new added column will be the last column in the table and I don't want that. There is after so you can add after a specified column but it doesn't work in CE. It can't find the keyword after. This is my query: "ALTER TABLE Inverters ADD COLUMN NewC int after Product", this is the error: "There was an error parsing the query. [ Token line number = 2,Token line offset = 21,Token in error = after ]". – lucian Oct 19 '10 at 13:14
  • Alter Table in CE seems to be missing that functionality from looking here http://msdn.microsoft.com/en-us/library/ms174123.aspx Lacking this I'm not sure how you could preserve your current order short of recreating the table. Personally I wouldn't be overly worried about that as column ordering would be specified by my select statement anyway. – Robb Oct 19 '10 at 13:33
  • 2
    Perfect solution (unless the column is part of an index). Remember the data type on the first command, or the script will fail / fail / succeed... – Geoff Jun 11 '11 at 15:34
  • 1
    I had to add [newColumn] datatype e.g. [HandlebarsTemplate] ntext for this to work, otherwise thanks for 99.999% of the answer! – Colin Wiseman Jul 10 '20 at 11:04
  • 1
    @ColinWiseman Thanks for the nudge,I should probably have updated the answer 9 years ago when it was first pointed out. Updated now - better late than never – Robb Jul 10 '20 at 12:13
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/

Kamyar
  • 18,639
  • 9
  • 97
  • 171
  • Are you sure that it works with Compact Edition as well? I've tried it before posting this question and I've tried it using your example and it doesn't work. Here's what i got: The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ] – lucian Oct 19 '10 at 12:48
  • You're right. sorry. seems like sp_rename is not available in SQLCE. updating my answer... – Kamyar Oct 19 '10 at 12:55
  • 1
    Still I could use EXEC sp_rename 'demo', 'pets' successfully in MS Compact Edition 2008 for renaming table. But yes failed for renaming columns. – msinfo Nov 28 '13 at 05:23
  • @Kamyar, the link you added is now dead. – Doctor Jones Oct 15 '18 at 09:53
0

SDF Viewer has this function built in, you can also rename indexes, keys and relationships. Just right click on the name you want to change in the database treeview.

howard
  • 1