sp_rename is the system stored procedure used to rename user created objects like “Stored procedure“, “Trigger“, “Table“ etc., This SP works pretty fine as long as you don't use it to rename a “Stored proc“, “Trigger“ or a “View“. Let me try and explain this with an example. Normally once a SP, Trigger or a View is created an entry is made into sysobjects as well as syscomments tables. For better understanding follow the following steps (examples uses Pubs database). Step 1: /* Description: Sample procedure to demonstrate sp_rename issue Author: M. Vadivel Date: August 12, 2004 */ Create Procedure FetchAuthorsDetails As Select * from Authors Step 2: [self explanatory] /* Description: Query to see the stored proc details in sysobjects Author: M. Vadivel Date: August 12, 2004 */ Select [name] from sysobjects where name like 'FetchAuthors%' Step 3: [self explanatory] /* Description: Query to see the stored proc details in syscomments Author: M. Vadivel Date: August 12, 2004 *
I write about things which I am passionate about.