MS Sql Update Field Names

[expired user #8067]'s profile image [expired user #8067] posted 10 years ago in Running SQL scripts Permalink
I tried to update the column name for a field in a table in SQL Server. I received the following error.

SQL Error (4902): Cannot find the object "dbo.state" because it does not exist or you do not have permissions.

The SQL code generated was this:

ALTER TABLE "dbo"."state" ALTER COLUMN "circuit_court1" VARCHAR(255) NULL;

I saw a recommendation to use the sp_rename for changing a field name in SQL Server.

EXEC sp_RENAME 'table_name.old_field_name','new_field_name','COLUMN'

That command worked. Wanted to let you know in case there was a away to implement that into your program.
ansgar's profile image ansgar posted 10 years ago Permalink
I was working on exactly that situation some time ago. The "EXEC sp_rename" approach was not working on all MSSQL versions. But when you tell me which version you have then probably I can make a version conditional.
[expired user #8067]'s profile image [expired user #8067] posted 10 years ago Permalink
I was using this on SQL Server 2012 Developer Edition. The blog I found it on said they were using SQL Server 2008 R2, but I don't have that setup for me to test it.
[expired user #8067]'s profile image [expired user #8067] posted 10 years ago Permalink
Going through the "Other Versions" on this page:
http://technet.microsoft.com/en-us/library/ms188351(v=sql.105).aspx

it seems like the sp_rename should work on versions from 2005 up.
ansgar's profile image ansgar posted 10 years ago Permalink
Ok, then we probably need an alternative for MSSQL 2000, if the current implementation does not do it.

Please login to leave a reply, or register at first.