Renaming Objects in SQL Server

It is extremely rare to rename any database object once it has been defined; however, if the need arises, one must be aware of how to do so.

Renaming a stored procedure or user-defined function does not change the name of the corresponding object name in the sys.sql modules catalog view’s definition column. As a result, it is recommended that such object types not be renamed. It is preferable to delete the stored procedure and rename it.

Similarly, changing the name or definition of a user-defined function can cause dependent objects to fail if the objects aren’t updated to reflect the function’s changes.

RENAME DATABASE:
The following commands will help in modifying the database name.
ALTER DATABASE [dbTest] MODIFY NAME = [TestDB]
EXEC sp_renamedb ‘dbTest’, ‘TestDB’

RENAME TABLE:
The following commands will help in modifying the database name.
USE TestDB;
GO
EXEC sp_rename ‘StudentsInformation’, ‘StudentInfo’;

RENAME A VIEW:
Similar to tables, sp rename can be used to rename the VIEW, but it is recommended that the existing view be deleted and then recreated with the new name.
EXEC sp_rename ‘vwStudentsSumm’, ‘vwStudentSummary’;

RENAME A STORED PROCEDURE:
The following command will help in modifying the stored procedure name.
EXEC sp_rename ‘uspGetAllStudentsInformation’, ‘uspGetStudentsInfo’;

If you don’t want to use the commands, go to Object Explorer, right-click on the object whose name you want to change, and select rename.

I hope you found this article useful.
Please subscribe if you want to receive more interesting updates.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s