Clear all table records in the database and restore the record ID from 0

Recently, I found that the database is too large and has insufficient space. Therefore, I plan to clear all the data in the database, but there are a lot of tables, one by one, which is really troublesome, therefore, we want to use SQL statements to clear all data at a time. three methods are found for clearing. the database used is ms SQL SERVER.

1. Search all table names and construct an SQL statement.

Declare @ trun_name varchar (8000)

Set @ trun_name =''

Select @ trun_name = @ trun_name + 'truncate table' + [name] + ''from sysobjects where xtype = 'U' and status> 0

Exec (@ trun_name)

This method is applicable when there are not many tables. Otherwise, the number of tables exceeds the length of the string and cannot be completely cleared.

2. Use a cursor to clear all tables

Declare @ trun_name varchar (50)

Declare name_cursor cursor

Select 'truncate table' + name from sysobjects where xtype = 'U' and status> 0

Open name_cursor

Fetch next from name_cursor into @ trun_name

While @ FETCH_STATUS = 0

Begin

Exec (@ trun_name)

Print 'truncated table' + @ trun_name

Fetch next from name_cursor into @ trun_name

End

Close name_cursor

Deallocate name_cursor

This is self-built and can be called as a stored procedure. It can clear all the table data at a time, and it can also be selected to clear the table.

3. Use Microsoft's undisclosed stored procedures

Exec sp_msforeachtable "truncate table? "