Clear records of all tables in the specified database

Except the records of all tables in the specified database, an empty database is required.

The method is as follows:

I:

Comparison between Delete and truncate table in SQL

Delete Applies to the deletion of a limited range, for example: delete titles Where type = "business" Because the delete operation writes transaction logs at the same time, the speed is slow. However, when a misoperation or disaster occurs Can be restored at birth. Delete is not recommended for deletion without a limited range, because the speed is really slow. Truncate table During execution, it simply removes all the space allocated to a table and its indexes, instead Delete a table in one row. This method is essentially faster than the unlimitedly deleted Many, especially when performing operations on a large table. The reason why truncate table executes fast is that it does not record a single Delete rows. However, after the truncate table is used, the database can be restored in the event of a disaster. Power is compromised. Unless you first run a full backup (dump database) Truncate During execution, it simply removes all the space allocated to a table and its indexes, instead Delete a table in one row. This method is essentially faster than the unlimitedly deleted Many, especially when performing operations on a large table. The reason why truncate table executes fast is that it does not record a single Delete rows. However, after the truncate table is used, the database can be restored in the event of a disaster. Power is compromised. Unless you first run a full backup (dump database) Truncate Table is the most commonly used method. The Delete and truncate tables Delete only the rows in the table and do not Delete the definition of the entire table, To delete the definition of the entire table, use drop table. Note: For tables that contain identity column (automatic increment or decrement), use When deleting a row in Delete, the initial values of identity column are not restored. For example, ID is the table title. In identity column, seed value is 1, and incerment is 1. The table contains 100 records, The ID is already 100. After deleting the delete operation, add a record with the ID of 101. If you use The initial values of the truncate table and identity column are automatically restored. Add a record with the ID of 1.

II:

1.Delete

Declare @ SQL varchar (5000)
Set @ SQL =''
Select @ SQL = @ SQL + 'delete from' + [name] + ';' from sysobjects where xtype = 'U'
Print @ SQL
Exec (@ SQL)

2. turncate

(1) Declare @ SQL varchar (5000)
Set @ SQL =''
Select @ SQL = @ SQL + 'truncate table' + [name] + ';' from sysobjects where xtype = 'U'
Print @ SQL
Exec (@ SQL)

(2) Use a cursor

---- Clear all records in each table of the specified database