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?
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;