MS Sql Update Field Names

sbaer posted 6 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 6 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 5 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 5 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 5 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.