Tuesday, August 23, 2011
This example demonstrates how to rename the store procedure using Transact-SQL. Using sp_rename system procedure, you can rename the store procedure.
SP_RENAME, '[OLD NAME]','[NEW NAME]'
Step 1: I have created a store procedure with named testing as shown below.
Step2: Use SP_HELPTEXT system procedure to see the store procedure as shown below.
Step 3: I have renamed the store procedure by using build in system store procedure named “SP_RENAME” as shown in figure.
The above query will rename the store procedure from testing to xp_testing.
Step 4: Let us, again used the SP_HELPTEXT to see the xp_testing store procedure as shown below.
In the above example, you have seen that we have renamed the store procedure from testing to xp_testing. In fact, this will change the store procedure name but it will not update in the system procedure table, so I will not prefer to use sp_rename to rename the user define procedure, you must have to delete the store procedure and re-create it with new name.
Note: I will recommend you that do not use the sp_rename to rename the store procedure, first drop the store procedure and then recreate the store procedure with name name.