6

I tried to run the following commands in SQL DW:

RENAME OBJECT dbo.test TO test2
RENAME OBJECT test TO test2

Both failed with the following error:

No item by the name of '[DemoDB].[dbo].[test]' could be found in the current database 'DemoDB', given that @itemtype was input as '(null)'.

Is this a defect or is there a workaround that I can use?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nicolle Anger - MSFT
  • 333
  • 1
  • 3
  • 10

2 Answers2

9

RENAME is now supported. In order to use RENAME OBJECT you must prefix the table you want to change with the schema name like this:

RENAME OBJECT x.T_New TO T;

Notice that there is no schema qualification on the target. This is because the renamed object must continue to reside inside the same schema. To transfer a table from one schema to another you need to use the following command:

ALTER SCHEMA dbo TRANSFER OBJECT::x.T_NEW;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Nicolle Anger - MSFT
  • 333
  • 1
  • 3
  • 10
0

In case someone else is looking at the time. It posible now in Azure Synapse Analytics, formely Azure SQL Datawarehouse; you can go with:

ALTER DATABASE AdventureWorks2012
MODIFY NAME = Northwind;
Josef
  • 2,869
  • 2
  • 22
  • 23
Gian Carlos Liriano Adames
  • 1