Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rename Object not supported in Azure SQL Data Warehouse?

[Posting question from customer on an internal thread]

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?

like image 710
Nicolle Anger - MSFT Avatar asked Aug 26 '15 20:08

Nicolle Anger - MSFT


People also ask

Can you rename an object in SQL?

You can use the sp_rename stored procedure or use SQL Server Management Studio by right clicking on the object and selecting rename. Another option is to create a new object and drop the old object. This could be any object that exists with SQL Server (table, stored procedure, trigger, etc.)

Which is not supported in SQL Azure?

T-SQL statements related to availability groups are not supported by SQL Database, and the dynamic management views related to Always On Availability Groups are also not supported. For a list of the features that are supported and unsupported by SQL Database, see Azure SQL Database feature comparison.

How do I rename a table in Azure SQL Server?

To rename a database in Azure SQL Database, use the ALTER DATABASE (Azure SQL Database) statement. To rename a database in SQL Server, use the stored procedure sp_renamedb.


1 Answers

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;

like image 178
Nicolle Anger - MSFT Avatar answered Nov 06 '22 00:11

Nicolle Anger - MSFT