SQL deletes all tables and DATA statements in the database

It is not difficult to use SQL to delete all tables in the database, that is, to traverse all user tables in the database and clear them. The following is a specific SQL statement, A detailed comment has been made on the key part:

The code is as follows:

Copy code

-- Variable @ tablename: name of the table to be saved Declare @ tablename nvarchar (100) -- Save all user tables to a temporary table # tablename SELECT [name] into # tablename FROM sysobjects WHERE type = 'u '; -- When # tablename has data While (select count (1) from # tablename)> 0 Begin -- Obtain the first entry from # tablename Select top 1 @ tablename = [name] from # tablename; -- Delete a table. The table name is a variable, so dynamic SQL is used here. Exec ('drop table' + @ tablename ); -- Delete the table name record from # tablename Delete from # tablename where [name] = @ tablename; End -- Delete the temporary table # tablename

Drop table # tablename: the SQL statement does not use a cursor, but uses a temporary table for traversal. In this case, the SQL statement is used to clear all tables in the database.

Another method

It is convenient to delete all data tables in the database and clear the database. There are some constraints that cannot be deleted directly. You need to delete the constraints in the database first. The code is as follows:

 

The code is as follows:

Copy code

-- Delete all constraints DECLARE c1 cursor Select 'alter table ['+ object_name (parent_obj) +'] drop constraint ['+ name +'];' From sysobjects Where xtype = 'F' Open c1 Declare @ c1 varchar (8000) Fetch nextfrom c1 into @ c1 While (@ fetch_status = 0) Begin Exec (@ c1) Fetch nextfrom c1 into @ c1 End Close c1 Deallocate c1 -- Delete all tables in the database Declare @ tname varchar (8000) Set @ tname ='' Select @ tname = @ tname + Name + ', 'From sysobjects where xtype = 'u' Select @ tname = 'drop table' + left (@ tname, len (@ tname)-1) Exec (@ tname)

Then clear all tables in the database:
To delete a stored Procedure, change where xtype = 'u' to where xtype = 'p' and drop table to drop Procedure.

Attach clear all data in the data table

Clear all data. Three methods are found to clear the data. The database used is ms SQL SERVER.
1. Search all table names and construct an SQL statement.

The code is as follows:	Copy code
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

The code is as follows:	Copy code
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

The code is as follows:	Copy code
Exec sp_msforeachtable "truncate table? "