How to rename a table in SQL pool database

Question

Hi

I am trying to rename an existing table in Azure Synapse SQL data store.

I do not have any 3rd party tools or add-ons.

Is there a script of steps I can use to rename tables?

Thank you.

Answer 1

Hi @Gunawardena, Udeshka
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
Ask: I am trying to rename an existing table in Azure Synapse SQL data store.

I do not have any 3rd party tools or add-ons.

Is there a script of steps I can use to rename tables?

Solution: We were able to use following in Synapse to rename tables:

RENAME OBJECT SCHEMA.CURRENT_TABLE_NAME TO NEW TABLE NAME

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community.

Answer 2

Hi Gunawardena, Udeshka

In Azure Synapse SQL, there isn't a direct command to rename a table you can use the effect of renaming a table by using a combination of creating a new table, copying the data, and dropping the old table. ill tell you how

first, create a table

CREATE TABLE dbo.NewTableName (     -- Define columns similar to the existing table     Column1 INT,     Column2 VARCHAR(255),     -- ... ); 

Then copy the data from the old table

INSERT INTO dbo.NewTableName (Column1, Column2, ...)
SELECT Column1, Column2, ...
FROM dbo.OldTableName;

You can run this verify to check if the old data is the same as in new table

SELECT * FROM dbo.NewTableName;

Now drop the old table

DROP TABLE dbo.OldTableName;

Thats it, if you find this answer useful kindly upvote and accept the answer thanks much.