Replacing Object Definitions

Replace existing compiled object definitions with new definitions while preserving the original names, object IDs, security attributes—such as auditing options and permissions— and replication attributes.

The create or replace functionality creates a new object if it does not exist, or replaces an existing object with the same name. The or replace clause implicitly drops and re-creates an existing object of the same name and type within the database, changing the definition of the object, while preserving the existing security and replication attributes. If the text of the compiled object was hidden before it was replaced, it will remain hidden after being replaced.

The or replace functionality is supported only for objects that do not contain data. Check constraints, computed columns, and partition conditions cannot be replaced.

If the object is in use while being replaced, error 3702 is raised:

"Cannot drop or replace the %S_MSG '%.*s' because it is currently in use."

When an object is replaced, SAP ASE replaces its definition in the following system tables: sysprocedures, syscomments, sysdepends, and syscolumns. Some fields in the sysobjects table are also updated. The query tree for the object is normalized before being replaced in sysprocedures.

The replaced object may be used in other object definitions. SAP ASE recompiles the replaced object when it is used, however, in some cases, you may need to replace the calling object when the interface of the replaced object does not match with that used in the calling object. You can run sp_depends on the replaced object to verify whether there are calling objects and then replace them. For details, see Objects Dependent on Replaced Objects, in Reference Manual: Commands:

create procedure
create view
create function

With granular permissions enabled or disabled, you must be the object owner to replace a compiled object. You cannot replace a compiled object by impersonating the object owner through an alias or setuser. However, if you are the owner through set proxy, you can replace a compiled object.

Note: The create or replace functionality performs an implicit drop followed by create in the same transaction. Because of this, additional transaction log space is required. If you use create or replace instead of dropping an object and then creating the object, you may need to increase the size of the transaction log.