Cache DDL syntax to rename a Table
Hi All, I am a Data Architect and am trying a method of deploying alter statements onto Cache DB.
My Data Modeling tool generates alters as this.. So question is if the Cache has a way to rename table? If so what is it?
ALTER TABLE <SchemaName>.GROUP_TYPE_NODES RENAME TO GROUP_TYPE_10082019140110000
;
This is giving error.
SQL Error [25] [37000]: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (IDENTIFIER) encountered after end of query^ALTER TABLE xxx.GROUP_TYPE_NODES RENAME>]
keyword RENAME TO is not supported by ALTER TABLE see docs.
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_altertable
But you can rename a table using Studio or Atelier.
...or using a stored procedure along these lines:
ClassMethod RenameTable(oldName As %String, newName As %String) As %String [ SqlProc ]
{
try {
&sql(select %ID into :className
from %Dictionary.ClassDefinition
where SqlTableName = :oldName
)
if SQLCODE set status = "Error: Table '" _ oldName _ "' not found." quit
set classDef = ##class(%Dictionary.ClassDefinition).%OpenId(className)
set classDef.SqlTableName = newName
set saveStatus = classDef.%Save()
set status = $case(saveStatus, 1: "OK", : "Error: " _ $system.Status.GetErrorText(saveStatus))
} catch {
set status = "Error: " _ $zerror
}
return status
}
Note: This doesn't handle the special case where there is no 'SqlTableName' defined.
Yes I tried but no success
ALTER TABLE SD.GROUP_TYPE_NODES GROUP_TYPE_10082019140110000
;
SQL Error [25] [37000]: [SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (IDENTIFIER) encountered after end of query^GROUP_TYPE_10082019140110000>]
not sure of how to use Studio.