To rename columns in a SQL Server database, you can use sp_rename. The syntax of the command, in Transact-SQL-ese, is:
sp_rename
[ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
So say you have a table called t_est, with a column in it called est_client. You want to rename these to t_job and job_client respectively. Firstly, with some trepidation, you rename the table:
sp_rename 't_est', 't_job'
You find, miracle of miracles, that it works! SQL Server warns you about stored procedures breaking, but looking at other dependent tables you find that foreign key constraints have all been updated so you still have referential integrity.
At this point, you might try renaming a column called, say, est_client to job_client as follows:
sp_rename 'est_client', 'job_client'
That's wrong, of course, as you soon find out: the object_name needs to be qualified to distinguish it from . So you might try:
sp_rename 't_job.est_client', 't_job.job_client'
Well, that was successful, but: oh! You now have a column called t_job.job_client, in the t_job table. It looks like new_name shouldn't have been qualified after all: you should have assumed inconsistency where consistency was implied by the spec. Well, dealing with Microsoft's tech documentation is certainly a learning experience at the best of times, so chalk this one up to experience. But if you now try to learn from your mistakes and revert the column name, you find you can't, because:
sp_rename 't_job.t_job.job_client', 'job_client'
tries to rename the column job_client, owned by user t_job, in table t_job... to job_client. After much headscratching, you finally realise that you can employ double quotes to qualify the column name and make SQL Server treat the dot as a literal dot and not a separator:
sp_rename 't_job."t_job.job_client"', 'job_client'
You sit back, grudgingly satisfied. More time passes. Spring arrives. The owls are not what they seem.
