Here in this Blog I have mentioned how to rename a object in the database.
while renaming a database name It should be changed to single user.
Renaming a table,Stored procedure,function,view,trigger uses same the way sp_renamedb
how ever renaming stored procedure,function,view and trigger is not a good option.
SQL server will not update sys.syscomments table while renaming objects.
so when ever we use sp_helptext to retrieve a Stored procedure or a function.
you can clearly see that the Stored procedure or the function name is a old one.
If you want to rename a object better drop and create again.
see for samples below..
Here we GO...!!!
Renaming a Database
exec sp_dboption DBname_OLD ,'Single User',True
exec sp_renamedb 'DBname_OLD','DBname_NEW'
exec sp_dboption DBname_NEW,'Single User',False
Renaming a Table
Renaming a column
Renaming a Stored Procedure,Function,View,Trigger
In the below image I have created a procedure named SPTEST and renamed to SPTEST_NEW
But still when I use sp_helptext to retrieve the procedure it shows us the old Procedure name.
this is because sys.syscomments table has not been updated.
here either we can drop and create the procedure or we can alter the procedure from the SSMS window by right click on it.