sp_dropobjectdef

Syntax

sp_dropobjectdef tablename

tablename – has the form dbname.owner.object, where:
- dbname is the name of the database containing the object with a storage location that you are dropping. dbname is optional; if present, it must be the current database, and the owner or a placeholder is required.
- owner is the name of the owner of the object with a storage location that you are dropping. owner is optional; it is required if dbname is specified.
- object is the name of the local table for which external storage mapping is to be dropped.

Example 1 – Deletes the entry from sysattributes that provided the external storage mapping for a table known to the server as the colleges table in database personnel:
```
sp_dropobjectdef "personnel.dbo.colleges"
```
Example 2 – Deletes the entry from sysattributes that provided the external storage mapping for the andrea.fishbone object, where andrea is the owner and the local table name is fishbone:
```
sp_dropobjectdef "andrea.fishbone"
```

There are additional considerations when using sp_dropobjectdef:

sp_dropobjectdef deletes the external storage mapping provided for a local object. It replaces sp_droptabledef.
Use sp_dropobjectdef after dropping a remote table with drop table.
Dropping a table does not remove the mapping information from the sysattributes table if it was added using sp_addobjectdef. It must be explicitly removed using sp_dropobjectdef.
The tablename can be in any of these forms:
- object
- owner.object
- dbname..object
- dbname.owner.object

See also create existing table, create table, drop table in Reference Manual: Commands.

The permission checks for sp_dropobjectdef differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the object owner or a user with `drop any table` privilege.
Disabled	With granular permissions disabled, you must be the object owner, the database owner, or a user with sa_role.

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect