MS Sql Update Field Names

sbaer posted 11 months ago in Running SQL scripts
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 posted 11 months ago
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.
sbaer posted 10 months ago
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.
sbaer posted 10 months ago
Going through the "Other Versions" on this page:

it seems like the sp_rename should work on versions from 2005 up.
ansgar posted 10 months ago
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.