Using sp_rename

In my previous post I talked about using sp_rename to rename columns and primary keys/indexes. There was a time when most people recommended that you don’t use sp_rename and if you do a internet search for sp_rename you will see articles such as this one that Andras wrote back in 2006. Things do move on, so when you find a recommendation like this, I would recommend that check that it still applies to the version of SQL Server you are using. You may also find that an article does not explain the whole reason or be vague on the consequences. For example why is it so important that the procedure text held in syscomments so important? If SSMS has a work around when scripting or modifying a procedure, what other problems could it cause? Certain tools may use the text in syscomments to compare two versions of a procedure, but then I would ask if/why such tools should were being used production systems? Surely the definitive stored procedure/view definitions are the ones in version control?

Re-creating views or stored procedures will be less of an issue than dropping/re-creating primary keys, but even with primary keys it is not a black and white decision what you should do. If there is little data in the table or if there are no other indexes on the table you may still want to drop/create the index. If that is the case you would need to remind me again why you are wanting to script the objects from a production system!!

So whether you should use sp_rename for the problem cases above is not a clear one, and you may be the case that you make two different decissions depending on what the target system is!