Rename table in DB Project

Question

I am fairly new to database projects - in my solution i have a db project where i want to rename all the table to remove a prefix. I want to add this to the project so i can then repeat in UAT and then live environments. If i just rename the table in the TSQL window, it just creates a new table rather than renaming the existing table. I want the project to still be able to rebuild the database from scratch if required.

Hope that makes sense!
Cheers

Answer 1

You cannot rename a DB table without it being a breaking change. Therefore in a migration situation the existing tables would remain as they cannot be removed. In order to remove the table(s) all references would need to be dropped, the table renamed and then all the references added back. Given that this can be cascading it would be very expensive.

The only supported way, that I'm aware of, to rename a table is to use the sp_rename sproc. However if you read the docs on this it is strongly recommended that you do not use this as it won't fix up the references. The recommendation is to create the new table, update the references and then drop the old table.

Answer 2

Hi ,@Steve Canvin

Although it can be implemented with TSQL, the official documentation also points out that this is risky, here is my code, please try:

CREATE TABLE TestOrderList  
(   
Id INT PRIMARY KEY IDENTITY(1, 1),   
OrderCode VARCHAR(100),   
OrderName VARCHAR(100), DueDate DATETIME  
)  
INSERT INTO  TestOrderList  
VALUES  
( 'X120', 'Notebook', '20191212' ),   
( 'W201', 'PC', '20191120' ),   
( 'KWW901', 'Printer', '20191001' )  
GO   
SELECT * FROM TestOrderList  

DECLARE @RetVal AS INT  
EXEC @RetVal=sp_rename 'dbo.TestOrderList', 'mysimple'  
SELECT @RetVal AS ReturnValue  

select *from simple  

Previous table--dbo.TestOrderList:
New Table----simple:

Reminder : Please be aware that this change may affect your table structure, I saw a way to use stored procedures many years ago, attach the link here.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

A refactor operation will rename an existing table during a publish to an existing database.

In your SSDT database project, select the table name in the T-SQL source code script and right click. Then select Refactor-->Rename from the context menu. Specify the new name and click OK to rename the table and dependent object references. Repeat for the other tables as needed.