Deletes a view.
The DROP VIEW command removes a view, but does not remove the underlying tables or data.
A drop view operation can also be invoked using the DropView()Opens in a new tab method call:
The DROP VIEW command is a privileged operation. Prior to using DROP VIEW it is necessary for your process to have either %DROP_VIEW administrative privilege or a DELETE object privilege for the specified view. Failing to do so results in an SQLCODE -99 error (Privilege Violation). You can determine if the current user has DELETE privilege by invoking the %CHECKPRIV command. You can determine if a specified user has DELETE privilege by invoking the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method. You can use the GRANT command to assign %DROP_VIEW privileges, if you hold appropriate granting privileges.
In embedded SQL, you can use the $SYSTEM.Security.Login()Opens in a new tab method to log in as a user with appropriate privileges:
You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.SecurityOpens in a new tab in the InterSystems Class Reference.
You can delete a view based on a table that is projected from a deployed persistent class.
To determine if a specified view exists in the current namespace, use the $SYSTEM.SQL.Schema.ViewExists()Opens in a new tab method.
By default, if you try to delete a nonexistent view, DROP VIEW issues an SQLCODE -30 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL DROP of non-existent table or view setting. The default is 0 (“No”). This is the recommended setting for this option. If set to 1 (“Yes”) issuing a DROP VIEW or DROP TABLE for nonexistent views and tables performs no operation and issues no error message.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.
The behavior of the predicate IF EXISTS takes priority over settings in the Management Portal and the configuration parameter file (CPF) which also govern DDL statementsOpens in a new tab. These settings return SQLCODE 0 and suppress the error silently. When IF EXISTS is specified, the command returns SQLCODE 1 along with a message.
VIEW Referenced by Other Views
If you try to delete a view referenced by other views in their queries, DROP VIEW issues an SQLCODE -321 error by default. This is the RESTRICT keyword behavior.
By specifying the CASCADE keyword, an attempt to delete a view referenced by other views in their queries succeeds. The DROP VIEW also deletes these other views. If InterSystems IRIS cannot perform all cascade view deletions (for example, due to an SQLCODE -300 error) no views are deleted.