Removes a table definition and all of its data, indexes, partition properties, triggers, encryption properties, and permissions from the database.
drop table [[database.]owner.]table_name [, [[database.]owner.]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 by the same 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.
Dropping a table drops any decrypt default associated with the table’s columns, and drops the columns’ encryption properties.
The system tables affected when a table is dropped are sysobjects, syscolumns, sysindexes, sysprotects, syscomments, syspartitions, syspartitionkeys, and sysprocedures.
If CIS 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:
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
Use sp_renamedb to rename either database.
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.
The permission checks for drop table differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be the table owner or a user with drop any table privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be the table owner or a user with sa_role. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
27 |
drop |
drop table |
|
Commands alter table, create table, delete, truncate table
System procedures sp_depends, sp_help, sp_spaceused
