Chapter 1: Commands

drop table

Removes a table definition and all of its data, indexes, partition properties, triggers, and permissions from the database.

drop table [[database.]owner.]table_name 
	[, [[database.]owner.]table_name ] ...

table_name: is the name of the table to drop. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

Removes the table roysched and its data and indexes from the current database:

drop table roysched

When you use drop table, any rules or defaults on the table lose their binding, and any triggers associated with it are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults and re-create any triggers.
When you drop a table, any partition condition associated with the table is also dropped.
The system tables affected when a table is dropped are sysobjects, syscolumns, sysindexes, sysprotects, syscomments, syspartitions, syspartitionkeys, and sysprocedures.
If Component Integration Services is enabled, and if the table being dropped was created with create existing table, the table is not dropped from the remote server. Instead, Adaptive Server removes references to the table from the system tables.

You cannot use the drop table command on system tables.
You can drop a table in any database, as long as you are the table owner. For example, use either of the following to drop a table called newtable in the database otherdb:
```
drop table otherdb..newtable
```
```
drop table otherdb.yourname.newtable
```
If you delete all the rows in a table or use the truncate table command, the table still exists until you drop it.

When you create a cross-database constraint, Adaptive Server stores the following information in the sysreferences system table of each database:

**Table 1-21: Information stored about referential integrity constraints**
Information stored in sysreferences	Columns with information about referenced table	Columns with information about referencing table
Key column IDs	refkey1 through refkey16	fokey1 through fokey16
Table ID	reftabid	tableid
Database name	pmrydbname	frgndbname

Because the referencing table depends on information from the referenced table, Adaptive Server does not allow you to:
- Drop the referenced table,
- Drop the external database that contains it, or
- Rename either database with sp_renamedb.
Use sp_helpconstraint to determine which tables reference the table you want to drop. Use alter table to drop the constraints before reissuing drop table.
You can drop a referencing table or its database. Adaptive Server automatically removes the foreign-key information from the referenced database.
Each time you add or remove a cross-database constraint or drop a table that contains a cross-database constraint, dump both of the affected databases.

WARNING! Loading earlier dumps of these databases can cause database corruption. For more information about loading databases with cross-database referential integrity constraints, see the System Administration Guide.

ANSI SQL – Compliance level: Transact-SQL extension.

drop table permission defaults to the table owner and is not transferable.

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
27	drop	drop table	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect