Two ways to rename SQL Server database objects

From time to time, a database object may need to be renamed for various reasons. When that happens, native features for renaming SQL Server database objects can be very useful. But, there are big differences between just renaming SQL Server database objects in the SQL Server Management Studio and Safe renaming them with ApexSQL Refactor.

This article will explain the differences between renaming database objects with SSMS and the ApexSQL Refactor’s Safe rename feature.

In the following example, let’s create the new database NewDB, and two tables Users and Address. The table Address will have Foreign references to the table Users:

CREATE DATABASE NewDB;

CREATE TABLE Users (
	UserID INT PRIMARY KEY IDENTITY(1, 1),
	FirstName VARCHAR(20),
	Lastname VARCHAR(50),
	Address VARCHAR(250)
	)
GO

CREATE TABLE Address (
	ID INT NOT NULL IDENTITY(1, 1),
	City VARCHAR(120),
	PostalCode INT,
	UserAddressID INT FOREIGN KEY REFERENCES Users(UserID)
	)
GO

Then create two stored procedures sp_GetUserAddress and sp_GetUserCity:

CREATE PROCEDURE sp_GetUserAddress
AS
BEGIN
	SELECT FirstName,
		Lastname,
		Address
	FROM Users
END
GO

CREATE PROCEDURE sp_GetUserCity
AS
BEGIN
	SELECT Users.FirstName,
		Users.Lastname,
		Address.City
	FROM Users
	INNER JOIN Address ON Users.UserID = Address.UserAddressID
END
GO

And, at the end, create view v_Address01 and trigger trgAfterInsert:

CREATE VIEW [dbo].[v_Address01]
AS
SELECT ID,
	City,
	PostalCode,
	UserAddressID
FROM dbo.Address
INNER JOIN Users ON Users.UserID = Address.UserAddressID
GO

CREATE TRIGGER trgAfterInsert ON [dbo].[Users]
FOR INSERT
AS
PRINT 'Data entered successfully'
GO

Renaming database objects with SSMS

To see dependencies for the table Users, select it in the Object Explorer and choose from the context menu View Dependencies command.

This command will open the Object Dependencies window for selected table, in this case for the table Users. In the list in the Object Dependencies window are all objects from the database NewDB, that depend on the table Users:

Some of the referenced objects have schema-bound dependencies to the table Users (trigger trgAfterInsert and table Address), and some of them have non-schema-bound dependencies (stored procedures sp_GetUserCity and sp_GetUserAddress, and view v_Address01).

Database objects that have schema-bound dependencies to the table Users will automatically accept all changes made. After renaming the table Users, there won’t be any dependency related errors.

But, the name modification of the table Users, will make non-schema bound dependencies objects invalid. To use these database objects after the table Users is renamed, their code must be modified manually.

To see that in example, select the table Users in the Object Explorer window and from the context menu click the Rename command:

Enter new name for the table Users, for example NewUsers and refresh database NewDB. In the table list it will be renamed as NewUsers:

After the new name for the table is entered, and database is refreshed, the list of referenced objects will change. Check if dependencies are changed for the renamed table NewUsers. Once again, select table NewUsers and click the View Dependencies command:

The list of referenced objects is now changed.

The stored procedure sp_GetUserAddress is no longer on the list of the referenced objects. Execution of this stored procedure will end with following message:

Msg 208, Level 16, State 1, Procedure sp_GetUserAddress, Line 4 [Batch Start Line 23]
Invalid object name ‘Users’.

On the other hand, the stored procedure sp_GetUserCity and the view v_Address01 are still on the list, but when these are used, errors will be raised:

Msg 208, Level 16, State 1, Procedure sp_GetUserCity, Line 8 [Batch Start Line 2]
Invalid object name ‘dbo.Users’.

Msg 208, Level 16, State 1, Procedure v_Address01, Line 8 [Batch Start Line 0]
Invalid object name ‘dbo.Users’.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function ‘v_Address01’ because of binding errors.

This proves that renaming a table, with SSMS, will not automatically update all database objects that refer to it. The code of the objects that have non-schema bound dependencies to the table Users, must be manually updated. Until every objects’ script is opened and every reference to the table Users changed to NewUsers, you cannot continue using these objects without errors.

Renaming database objects with ApexSQL Refactor

ApexSQL Refactor, Visual Studio and SSMS add – in, provides over 200 formatting options and 15 code refactors for formatting and refactoring SQL code and database objects.

One of the ApexSQL Refactor’s SQL code refactoring features is Safe rename. This feature provides renaming SQL Server’s objects without breaking the database dependencies. Database objects which can be renamed with this ApexSQL Refactor feature are: tables, views, procedures, functions, table/view columns and procedures/function parameters.

To see how the Safe rename feature works, select the table Users in the Object Explorer and from the context menu click Safe rename command:

Or choose the same option from the ApexSQL Refactor main menu:

The Safe rename window will appear:

In the fields, New schema and New name are the schema and the name of the selected table.

Notification – yellow blinking triangle – shows that table with this schema and name already exists.

In this window, table schema can be changed by choosing one of the existing database schemas from the drop-down list. When renaming a table with SSMS this is not one of the options. Because of that, for this example table schema will not be changed.

Insert new table name in the field New name. The important thing to know is that there are not any restrictions for characters in use. For this example, the new name for the table Users will be NewUsers.

Click the Preview button, located in the lower left corner of the Safe rename window.

Under the Generated script tab is T-SQL refactoring script which after execution will rename the table Users. In this window, the script cannot be changed, it can only be reviewed:

All warnings related to the renaming table, will be under the Warnings tab. For this example, there are no warnings:

To check all actions which will be executed for renaming the table Users, click on the Sequence tab. All actions are ordered in that way, so their executions will prevent breaking dependencies between the objects:

Under the Dependencies tab, there is a list of all referenced database objects to the table Users. ApexSQL Refactor does not make a difference between schema-bound and non-schema bound dependencies objects, because all referenced objects will be updated regardless of the dependencies type to the table Users:

For renaming the table Users simply click the Create script button. The generated script will be opened in the SSMS Query editor window:

In the Query editor, the script can be modified or it can be executed as is. After execution, the following message will appear:

Caution: Changing any part of an object name could break scripts and stored procedures.

After refreshing database NewDB in the table list, instead of the table Users will be the table NewUsers, same like after renaming table with SSMS, but with one big difference – none of the referenced objects are not invalid.

Just for check, if dependencies for the renamed table NewUsers are checked with the SSMS View dependencies feature, this list will appear:

This list is exactly the same as the first one, before changing the name of the table with ApexSQL Refactor.

With ApexSQL Refactor’s Safe rename feature it is easier to rename the table and what is more, without any additional work for changing all referenced objects. Also, in one window all things regarding renaming can be checked (warnings, sequences, dependencies).

This example showed how to rename a table, but the same steps are for renaming any of the SQL Server database objects (table/view columns, function/procedures parameters, views, functions, procedures).

Useful resources:

• Safe rename SQL objects, columns, and parameters
• How to rename database objects in a SQL Server database safely – Part 1
• How to rename database objects in a SQL Server safely – Part 2
• Understanding SQL Dependencies