Quickly delete data from all tables in a database

Source: Internet
Author: User
Tags reserved scalar

Quick deletion of data from all tables in a database

Select ' TRUNCATE TABLE ' + name + '; ' from sysobjects where xtype= ' U ' ORDER by Name ASC;

After the execution of this statement, all the tables in the database are queried, and the TRUNCATE statement is executed after copying it.

sysobjects

Each object created within the database (constraints, default values, logs, rules, stored procedures, and so on) occupies a single row in the table. Only within tempdb , each temporary object occupies one row in the table.

Column Name Data Type Description
Name sysname The name of the object.
Id Int The object identification number.
Xtype CHAR (2) The object type. Can be one of the following object types:

C = CHECK Constraint
D = defaults or DEFAULT constraints
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inline Table function
P = Stored Procedure
PK = PRIMARY KEY constraint (type is K)
RF = copy Filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User Table
UQ = UNIQUE constraint (type is K)
V = view
X = Extended Stored Procedure

Uid smallint The user ID of the owner object.
Info smallint Keep. Internal use only.
Status Int Keep. Internal use only.
Base_schema_
Ver
Int Keep. Internal use only.
Replinfo Int Keep. For replication use.
Parent_obj Int The object identification number of the parent object (for example, for a trigger or constraint, which is the table ID).
Crdate Datetime The date the object was created.
Ftcatid smallint Full-text catalog identifier for all user tables registered for full-text indexing, or 0 for all user tables that are not registered.
Schema_ver Int The version number, which is incremented each time the schema of the table changes.
Stats_schema_
Ver
Int Keep. Internal use only.
Type CHAR (2) The object type. Can be one of the following values:

C = CHECK Constraint
D = defaults or DEFAULT constraints
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inline Table function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored Procedure
R = Rule
RF = copy Filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User Table
V = view
X = Extended Stored Procedure

Userstat smallint Keep.
Sysstat smallint Internal state information.
Indexdel smallint Keep.
Refdate Datetime reserved for later use.
Version Int reserved for later use.
Deltrig Int Keep.
Instrig Int Keep.
Updtrig Int Keep.
Seltrig Int Keep.
Category Int Used for publishing, constraints, and identification.
Cache smallint Keep.
Truncate and delete without a WHERE clause, and drop deletes the data in the table

Different points:
1. Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.
The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger.
The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
Apparently the drop statement frees all the space occupied by the table.
Truncate statement by default see space released to minextents extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).
4. Speed, in general: drop>; Truncate >; Delete
5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
For use, to delete some data rows with delete, note the WHERE clause. The rollback segment should be large enough.
Want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/Insert Data

Quickly delete data from all tables in a database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.