[jira] [Commented] (DERBY-6205) Allow "RENAME [...] TABLE TO" an another schema.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6205?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13641859#comment-13641859 ] 

Rick Hillegas commented on DERBY-6205:
--------------------------------------

There may be a more general need here to move any kind of schema object from one schema to another schema. Of course, you can always create a new object, migrate the data, and drop the old object. But that is not efficient for large tables. It is also inconvenient for any schema object which has dependent objects. E.g., a function which is referenced by views, triggers, or generation clauses.

Moving an object from one schema to another may change the ownership of the object. That may have cascading GRANT/REVOKE implications.

For the original use case (moving a table to another schema), I find the following SQL Server syntax (see http://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql):

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;

The following syntax is suggested by http://www.pythian.com/blog/oracle-how-to-move-a-table-to-another-schema/:

RENAME TABLE sourceSchema.sourceTable TO targetSchema.targetTable

It's possible that the SQL committee may standardize syntax in this area. So any Derby syntax would have to be clearly marked in order to not conflict with future revs of the SQL Standard. Maybe something like the following:

RENAME DERBY objectType sourceSchema.sourceObject TO targetSchema.targetObject

E.g.:

RENAME DERBY FUNCTION mySchema.myFunc TO yourSchema.yourFunc
                
> Allow "RENAME [...] TABLE TO" an another schema.
> ------------------------------------------------
>
>                 Key: DERBY-6205
>                 URL: https://issues.apache.org/jira/browse/DERBY-6205
>             Project: Derby
>          Issue Type: Improvement
>            Reporter: Guillaume Chauvet
>
> It would be very useful to allow tables to be moved to an another schema with an extended syntax like this :
> RENAME TABLE table-Name TO {Another schema.}new-Table-Name
> This feature will be a must-have during a database refactoring phase.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira