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
use master
go
exec sp_dboption DBname_OLD ,'Single User',True
go
exec sp_renamedb 'DBname_OLD','DBname_NEW'
go
exec sp_dboption DBname_NEW,'Single User',False
Renaming a Table
sp_rename 'Tablename_OLD','Tablename_NEW'
Renaming a column
sp_rename 'Tablename.column_OLD','column_NEW','column'
Renaming a Stored Procedure,Function,View,Trigger
sp_rename 'Old_Name','New_Name'
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.
Hi Karthik,
ReplyDeleteAlso by explanation (in MSDN for Renaming SQL Objects):
--
Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, its recommended that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.
Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.
--
Having read that, I believe this sp_rename would rather create a SYNONYM instead of replacing it's name as such. Only at times, this do help us but in a very different way.
Thought I would add these points.
Thanks
Vaidy Mohan
Thanks Vaidy,
ReplyDeleteyour information regarding this blog is really helpful,While renaming a object we should check for the dependencies and in SQL server 2005
it uses "SYS.SQL_DEPENDENCIES" in SQL server 2008 it uses "SYS.SQL_EXPRESSION_DEPENDENCIES"
to see the depends
SELECT OBJECT_NAME(REFERENCED_MAJOR_ID),* FROM SYS.SQL_DEPENDENCIES
WHERE OBJECT_ID = OBJECT_ID('OBJECT_NAME')