Wednesday, August 15, 2007

Hidden gem: Rename table



This new feature is not mentioned anywhere, but listed in the docs:


sp_rename: Changes the name of a user table in the current database. Currently, sp_rename support in SQL Server Compact 3.5 is limited to tables.


sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ]
In the following example I will demonstrate it's use (using the VS 2008 beta 2 Server Explorer):







The current tablename is Test.


Run the sp_rename command, with the 2 parameters: Old name and new name


The query parser complains.








But runs the query none the less.








Do a refresh...


And voila... all from plain old T-SQL!






8 comments:

Adam Ruth said...

This appears to work in 3.1 as well.

ErikEJ said...

Yes, I does indeed. Thanks for pointing that out, Adam. It is apparently an undocumented feature of SQL Compact 3.1, which has now been documented.

Caio Cesar Moreira said...

Working also for CE 3.5.
LOL very funny and thanks for solving my problem!

BrutusBlog said...

great it works...

Vinu Gopinath said...

Where do i get that query analyzer(the second screen in the solution)?

ErikEJ said...

Select "New Query" from the context menu in Server Explorer

Vinu Gopinath said...

I'm using VS2008, I can't find the "New Query" in server explorer.

ErikEJ said...

Vinu: Just Right click on a SQL Server Compact connection