truncate table

Description

Removes all rows from a table or partition.

Syntax

truncate table [[database.]owner.]table_name 
	[partition partition_name]

Parameters

table_name

is the name of the table to truncate. 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.

partition_name

specifies the name of the partition to truncate.

Examples

Example 1

Removes all data from the authors table:

truncate table authors

Example 2

Removes all data from the smallsales partition of the titles table:

truncate table titles partition smallsales

Usage

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Permissions

truncate table permission defaults to the table owner. Table owners can grant permissions for truncate table to other users. To truncate a system audit table (sysaudits_01, sysaudits_02, sysaudits_03, and so on, through sysaudits_08), you must have the a system security officer role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

64

truncate

truncate 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 is in effect

See also

Commands alter table, create table, create trigger, delete, drop table, grant, revoke